-- This scripts contains following pcakage's definition:
-- =============================================================================
-- dbms_metadata            F   get_ddl(varchar,varchar,varchar,varchar,varchar,varchar,bool)
-- dbms_output              P   disable()
--                          P   enable(int4)
--                          P   new_line()
--                          P   put(text)
--                          P   put_line(text)
--                          P   get_line(text,int4)
--                          P   get_lines(text[],int4)
-- dbms_random              P   initialize(int4)    -- deprecated
--                          F   normal()
--                          F   random()    -- deprecated
--                          P   seed(int4)
--                          P   seed(text)
--                          F   string(text,int4)
--                          P   terminate()    -- deprecated
--                          F   value()
--                          F   value(numeric,numeric)
-- dbms_job                 P   broken(int8,bool,timestamp)
--                          P   change(int8,text,timestamp,text,int4,bool)
--                          P   instance(int8,int4,bool)
--                          P   interval(int8,text)
--                          P   next_date(int8,timestamp)
--                          P   remove(int8)
--                          P   run(int8,bool)
--                          P   submit(int8,text,timestamp,text,bool,int4,bool)
--                          P   user_export(int8,text)
--                          P   what(int8,text)
-- dbms_lock                P   sleep(float8)
-- dbms_application_info    P   read_client_info(text)
--                          P   read_module(text,text)
--                          P   set_client_info(text)
--                          P   set_module(text,text)
-- dbms_utility             P   canonicalize(text)
--                          P   comma_to_table(text,int4,text[])
--                          P   table_to_comma(text[],int4,text)
--                          P   db_version(text,text)
--                          P   exec_ddl_statement(text)
-- dbms_obfuscation_toolkit F   md5(text)
--                          F   md5(raw)  @todo

-- utl_url                  F   escape(text,bool,text)
--                          F   unescape(text,text)
-- utl_encode               F   base64_encode(raw)
--                          F   base64_decode(raw)
--                          F   text_encode(text,text,int4)
--                          F   text_decode(text,text,int4)
--                          F   quoted_printable_encode(raw)
--                          F   quoted_printable_decode(raw)
--                          F   mimeheader_encode(text,text,int4)
--                          F   mimeheader_decode(text)
--                          F   uuencode(raw,int1,text,text)
--                          F   uudecode(raw)
-- utl_raw                  F   cast_to_varchar2
--                          F   cast_to_raw(text)
--                          F   concat(raw,raw,raw,raw,raw,raw,raw,raw,raw,raw,raw,raw)
--                          F   length(raw)
--                          F   substr(raw,int4,int4)
--                          F   transliterate(raw,raw,raw,raw)
--                          F   translate(raw,raw,raw)
--                          F   copies(raw,int8)
--                          F   overlay(raw,raw,int4,int4,raw)
--                          F   xrange(raw,raw)
--                          F   reverse(raw)
--                          F   compare(raw,raw,raw)
--                          F   convert(raw,text,text)
--                          F   bit_and(raw,raw)
--                          F   bit_or(raw,raw)
--                          F   bit_xor(raw,raw)
--                          F   bit_complement(raw)
--                          F   cast_to_number(raw)
--                          F   cast_from_number(numeric)
--                          F   cast_to_binary_integer(raw,int1)
--                          F   cast_from_binary_integer(int4,int1)
-- dbms_lob                 F   getlength(blob)
--                          F   getlength(clob)
--                          P   open(blob,int)
--                          P   open(clob,int)
--                          P   freetemporary(blob)
--                          P   freetemporary(clob)
--                          F   substr(blob,int4,int4)
--                          F   substr(clob,int4,int4)
--                          F   instr(blob,raw,int4,int4)
--                          F   instr(clob,text,int4,int4)
--                          P   createtemporary(blob,bool,int4)
--                          P   createtemporary(clob,bool,int4)
--                          P   close(blob)
--                          P   close(clob)
--                          P   append(blob,blob)
--                          P   append(clob,clob)
--                          F   compare(blob,blob,int4,int4,int4)
--                          F   compare(clob,clob,int4,int4,int4)
--                          P   copy(blob,blob,int4,int4,int4)
--                          P   copy(clob,clob,int4,int4,int4)
--                          P   erase(blob,int4,int4)
--                          P   erase(clob,int4,int4)
--                          P   read(blob,int4,int4,raw)
--                          P   read(clob,int4,int4,text)
--                          P   trim(blob,int4)
--                          P   trim(clob,int4)
--                          P   write(blob,int4,int4,raw)
--                          P   write(clob,int4,int4,text)
--                          P   writeappend(blob,int4,raw)
--                          P   writeappend(clob,int4,text)
-- dbms_snapshot            P   refresh(text,text)

-- -----------------------------------------------------------------------------
--                          p   expand_sql_text  @todo
--                          p   get_dependency  @todo
--                          F   get_endianness  @todo
--                          F   get_hash_value  @todo
--                          F   get_parameter_value  @todo
--                          F   get_sql_hash  @todo
--                          F   get_time  @todo
--                          P   get_tz_transitions  @todo
--                          F   old_current_schema  @todo
--                          F   old_current_user  @todo
--                          F   port_string  @todo
-- dbms_lob                 P   converttoblob  @todo
--                          P   converttoclob  @todo
--                          P   loadblobfromfile  @todo
--                          P   loadclobfromfile  @todo
-- =============================================================================


set client_min_messages='warning';
set time zone 'UTC';
set statement_timeout = 60000;


-- =============================================================================
-- Create Schema (compat_tools)
-- =============================================================================
do $$
declare
    l_cnt    bigint;
begin
    select count(*) into l_cnt from pg_catalog.pg_namespace where nspname = 'compat_tools';
    if l_cnt = 0
    then
        execute 'create schema compat_tools';
    end if;
end;
$$ language plpgsql;


-- =============================================================================
-- Version Table
-- =============================================================================
create table if not exists compat_tools.compat_version
(
    compat_type     varchar(10),    -- VIEW, FUNCTION, PROCEDURE
    object_name     varchar(128),   -- Compat object name
    object_version  varchar(10),    -- Compat object version
    constraint pk_compat_version primary key(compat_type, object_name)
);


-- =============================================================================
-- Table: compat_tools.pg_function_list
-- =============================================================================
drop table if exists compat_tools.pg_function_list;
create table compat_tools.pg_function_list as
select p.oid
     , l.lanname as language
     , n.nspname as schema_name
     , p.proname || '(' || string_agg(case when a.typname is not null then a.typname||'[]' else t.typname end, ',' order by p.id) || ')' as function_name
  from (select oid
             , pronamespace
             , proname
             , prolang
             , case when proallargtypes is null then proargtypes else proallargtypes end as proallargtypes
             , generate_series(1, array_length(case when proallargtypes is null then proargtypes else proallargtypes end, 1)) as id
             , unnest(case when proallargtypes is null then proargtypes else proallargtypes end) as protype
          from pg_catalog.pg_proc
       ) as p
  join pg_catalog.pg_namespace as n on p.pronamespace = n.oid
  join pg_catalog.pg_language as l on p.prolang = l.oid
  join pg_catalog.pg_type as t on p.protype = t.oid
  left join pg_catalog.pg_type as a on t.typcategory = 'A' and t.typelem = a.oid   -- for array type
 group by p.proname, p.proallargtypes, l.lanname, p.oid, n.nspname
 union all
select p.oid, l.lanname, n.nspname, p.proname||'()'
  from pg_catalog.pg_proc as p
  join pg_catalog.pg_namespace as n on p.pronamespace = n.oid
  join pg_catalog.pg_language as l on p.prolang = l.oid
 where (oidvectortypes(p.proargtypes) is null or oidvectortypes(p.proargtypes) = '');


-- =============================================================================
-- Table: compat_tools.pg_depend_list
-- =============================================================================
drop table if exists compat_tools.pg_depend_list;
create table compat_tools.pg_depend_list as
select distinct dep.classid::regclass::text as object_type
     , coalesce(typ_n.nspname, coalesce(cls_n.nspname, dep.objid::text)) as schema_name
     , coalesce(typ.typname, coalesce(cls.relname, dep.objid::text)) as object_name
     , dep.refclassid::regclass::text as ref_object_type
     , cpt.object_name as ref_object_name
     , cpt.compat_type
     , case dep.deptype when 'n' then 'NORMAL' when 'a' then 'AUTO' when 'i' then 'INTERNAL' when 'e' then 'EXTENSION' when 'p' then 'PIN' when 'x' then 'AUTO_EXTENSION' when 'I' then 'INTERNAL_AUTO' else dep.deptype::text end as DEPENDENCY_TYPE
  from pg_depend as dep
  join (select v.compat_type, v.object_name, l.language, coalesce(l.oid, c.oid) as oid
          from compat_tools.compat_version as v
          left join compat_tools.pg_function_list as l on v.object_name = l.schema_name||'.'||l.function_name
          left join (select cls.oid, nsp.nspname||'.'||cls.relname object_name
                       from pg_catalog.pg_class as cls
                       join pg_catalog.pg_namespace as nsp on cls.relnamespace = nsp.oid
                    ) as c on v.object_name = c.object_name
         where v.compat_type in ('aggregate', 'procedure', 'function', 'view')) as cpt on dep.refobjid = cpt.oid
  left join pg_type as typ on dep.classid = 'pg_type'::regclass and dep.objid = typ.oid
  left join pg_namespace as typ_n on typ_n.oid = typ.typnamespace
  left join pg_rewrite as rwt on dep.classid = 'pg_rewrite'::regclass and dep.objid = rwt.oid
  left join pg_class as cls on rwt.ev_class = cls.oid
  left join pg_namespace as cls_n on cls_n.oid = cls.relnamespace
 where coalesce(typ_n.nspname, coalesce(cls_n.nspname, dep.objid::text)) != 'compat_tools';

-- =============================================================================
-- check extension conflict function
-- =============================================================================
CREATE OR REPLACE FUNCTION compat_tools.pg_describe_object2(oid, oid, INTEGER) returns text
  LANGUAGE plpgsql NOT FENCED NOT SHIPPABLE AS
  $$            
  DECLARE l_class_table text;
  l_object_name text;
  l_name_col    text;
  l_schema_col  text;
  l_schema_name text;
BEGIN
  SELECT relname, a.attname
    INTO l_class_table, l_name_col
    FROM pg_class c, pg_attribute a
   WHERE c.oid = a.attrelid
     AND relname NOT IN ('pg_attrdef', 'pg_cast', 'pg_rewrite')
     AND a.attnum = 1
     AND c.oid = $1 ;

  SELECT a.attname
    INTO l_schema_col
    FROM pg_class c, pg_attribute a
   WHERE c.oid = a.attrelid
     AND relname NOT IN ('pg_attrdef', 'pg_cast', 'pg_rewrite')
     AND a.attnum = 2
     AND c.oid = $1
     AND attname NOT LIKE '%owner';

  EXECUTE 'select ' || l_name_col || ' from ' || l_class_table || ' where oid = :1'
    INTO l_object_name
    USING  $2;
  EXECUTE 'select pn.nspname from pg_namespace pn,' || l_class_table || ' ct where pn.oid=ct.' || l_schema_col || ' and ct.oid=:1'
    INTO l_schema_name
    USING $2;

   if l_class_table='pg_proc' then
    select function_name into l_object_name from  compat_tools.pg_function_list where oid=$2;
   end if;
  
    RETURN l_schema_name || '.' || l_object_name;
exception when others then return '';
END; 
$$;

create or replace FUNCTION compat_tools.check_extension_conflict(text) RETURNS bool
  LANGUAGE plpgsql NOT FENCED NOT SHIPPABLE AS
  $$ 
  DECLARE 
   l_exists int4;
BEGIN
  SELECT COUNT(1)
    INTO l_exists
    FROM pg_depend t, pg_extension e
   WHERE t.refobjid = e.oid
     AND lower(compat_tools.pg_describe_object2(classid, objid, objsubid)) = lower($1);
  
   IF l_exists > 0 THEN 
     RETURN true;
   ELSE 
     RETURN false;
   END IF;
END; 
$$;

-- =============================================================================
-- Version Function
-- =============================================================================
drop function if exists compat_tools.drop_compat_package(varchar, varchar, varchar, varchar, varchar);
create or replace function compat_tools.drop_compat_package( p_object_type    varchar(10)
                                                           , p_package_name   varchar(128)
                                                           , p_object_name    varchar(128)
                                                           , p_object_version varchar(10)
                                                           , p_function_lang  varchar(16)  default 'plpgsql')
returns boolean
as $$
declare
    l_version    varchar(10);
    l_language   varchar(16);
    l_result     boolean       := 'true'; -- 是否可以在后续逻辑中继续创建这个对象
    l_operation  varchar(128);
    l_depend_cnt bigint;
    l_app_name   varchar(128)  := current_setting('application_name');
begin
    CREATE temporary table if not exists temp_result
    (
        object_type     varchar(10),
        object_name     varchar(128),
        local_version   varchar(10),
        script_version  varchar(10),
        local_language  varchar(10),
        script_language varchar(10),
        operation       varchar(128)
    ) ON COMMIT PRESERVE ROWS;

    -- 字符串参数统一转小写
    p_object_type := lower(p_object_type);
    p_package_name := lower(p_package_name);
    p_object_name := lower(p_object_name);
    p_function_lang := lower(p_function_lang);

    select max(object_version) into l_version
      from compat_tools.compat_version
     where object_name = p_package_name||'.'||p_object_name
       and compat_type = p_object_type;

    -- 若指定的 Schema 不存在 (对应 Oracle 中的 Package Name)，则自动创建
    select count(*) into l_depend_cnt from pg_catalog.pg_namespace where nspname = p_package_name;
    if l_depend_cnt = 0
    then
        execute 'create schema '||p_package_name;
    end if;

    -- 获取已有同名同参数函数/存储过程的语言，存入 l_language 变量
    select max(language) into l_language
      from compat_tools.pg_function_list
     where schema_name = p_package_name
       and function_name = p_object_name;

    -- 获取非 Compat Tools 依赖对象数量
    select count(*) into l_depend_cnt
      from compat_tools.pg_depend_list
     where ref_object_name = p_package_name||'.'||p_object_name;

    if l_language is null                      -- 系统中没有对应函数，必定创建
    then
        l_operation := 'Initial creation';
    elsif l_language != p_function_lang        -- 系统中函数语言与脚本中定义语言不一致，必须忽略
    then
        l_result = 'false';
        l_operation := 'Skip due to language';
    elsif l_version >= p_object_version        -- 系统版本表中版本不低于脚本中的版本号，必须忽略
    then
        l_result = 'false';
        l_operation := 'Skip due to version';
    elsif compat_tools.check_extension_conflict(p_package_name||'.'||p_object_name)           -- 系统中已有extension创建同名对象
    then 
        l_result='false';
        l_operation= 'Skip due to extension';
    else
        if l_version is null                   -- 版本表中无记录，可能是未创建，或者版本记录被误删除，尝试创建
        then
            l_operation := 'Initial creation (Ver)';
        else                                   -- 其他情况
            l_operation := 'Upgrade';
        end if;
    end if;

    if l_app_name != 'checkMe'
    then
        if l_result  -- 需要创建
        then
            begin
                -- 若系统中存在非 compat_tools 对象依赖本对象，无法删除，可尝试直接创建
                if l_depend_cnt = 0
                then
                    if instr(p_object_name, '(') > 0
                    then
                        execute 'drop '||p_object_type||' if exists "'
                             || p_package_name||'"."'||replace(p_object_name, '(', '"(')
                             || (case when p_object_type = 'FUNCTION' then ' cascade' else '' end);
                    else
                        execute 'drop '||p_object_type||' if exists "'
                             || p_package_name||'"."'||p_object_name||'"'
                             || (case when p_object_type = 'FUNCTION' then ' cascade' else '' end);
                    end if;

                    insert into compat_tools.compat_version
                    values ( p_object_type
                           , p_package_name||'.'||p_object_name
                           , p_object_version)
                    ON DUPLICATE KEY UPDATE object_version = p_object_version;
                else
                    l_operation := l_operation||' - dependence';
                end if;
            exception
                when others then
                    l_result := 'false';
                    get stacked diagnostics l_operation = message_text;
                    l_operation = substr(l_operation, 1, 32);
            end;
        else
            insert into compat_tools.compat_version
            values ( p_object_type
                   , p_package_name||'.'||p_object_name
                   , p_object_version)
            ON DUPLICATE KEY UPDATE NOTHING;
        end if;
    end if;

    -- 插入本次临时结果表
    insert into temp_result values ( p_object_type
                                   , p_package_name||'.'||p_object_name
                                   , l_version
                                   , p_object_version
                                   , l_language
                                   , p_function_lang
                                   , l_operation);

    -- 返回函数结果
    if l_app_name = 'checkMe'
    then
        return 'false';
    else
        return l_result;
    end if;
end;
$$ language plpgsql;


-- =============================================================================
-- Testing Table/Procedure
-- =============================================================================
create table if not exists compat_tools.compat_testing
(
    test_expr       text,      -- 测试表达式
    test_result     text,      -- 表达式结果
    expect_result   text,      -- 预期结果
    test_ok         bool,      -- 测试是否通过
    test_timestamp  timestamp  default now(), -- 测试时间
    constraint pk_compat_testing_expr primary key(test_expr)
);

drop procedure if exists compat_tools.f_unit_test(text, text, text, text);
create or replace procedure compat_tools.f_unit_test( p_test_expr     text
                                                    , p_expect        text
                                                    , p_compare_type  text  default '='
                                                    , p_db_compat     text  default NULL)
as
declare
    l_compat_valid bool    := 'true'::bool;
    l_error_code   text;
    l_error_mesg   text;
    l_test_result  text;
    l_test_ok      bool;
begin
    if current_setting('application_name') != 'checkMe'
    then
        if p_db_compat is not null
        then
            select count(*)::bool
              into l_compat_valid
              from pg_database
             where datname = current_database()
               and datcompatibility = p_db_compat;
        end if;

        if l_compat_valid
        then
            execute immediate 'select '||p_test_expr||', cast('||p_test_expr||' as text) '||p_compare_type||' '||p_expect
               into l_test_result, l_test_ok;
            insert into compat_tools.compat_testing
            values (p_test_expr, l_test_result, p_compare_type||' '||p_expect, l_test_ok)
            on DUPLICATE KEY UPDATE test_result = EXCLUDED.test_result
                                  , expect_result = EXCLUDED.expect_result
                                  , test_ok = EXCLUDED.test_ok
                                  , test_timestamp = now();
        end if;
    end if;
exception
    when others then
        get stacked diagnostics l_error_code = returned_sqlstate, l_error_mesg = message_text;
        insert into compat_tools.compat_testing
        values (p_test_expr, l_error_code||': '||l_error_mesg, p_compare_type||' '||p_expect, l_error_code||': '||l_error_mesg = trim(p_expect, ''''))
        on DUPLICATE KEY UPDATE test_result = EXCLUDED.test_result
                              , expect_result = EXCLUDED.expect_result
                              , test_ok = EXCLUDED.test_ok
                              , test_timestamp = now();
end;
/



-- =============================================================================
-- Version Comparison Function
-- Result:
--     version_a > version_b  => 1
--     version_a = version_b  => 0
--     version_a < version_b  => -1
-- =============================================================================
create or replace function pg_catalog.f_version_compare ( version_a text
                                                        , version_b text)
returns int IMMUTABLE strict as $$
declare
    l_rec   record;
begin
    if version_a !~ '^\d+(\.\d+)*$' or version_b !~ '^\d+(\.\d+)*$'
    then
        return null;
    end if;
    for l_rec in select coalesce(t1.ver, -1) as ver_1, coalesce(t2.ver, -1) as ver_2
                   from (select rownum as id, t1::int as ver from regexp_split_to_table(version_a, '\.') as t1) as t1
                   full outer join (select rownum as id, t2::int as ver from regexp_split_to_table(version_b, '\.') as t2) as t2
                     on t1.id = t2.id
    loop
        if l_rec.ver_1 > l_rec.ver_2
        then
            return 1;
        elsif l_rec.ver_1 < l_rec.ver_2
        then
            return -1;
        end if;
    end loop;

    return 0;
end;
$$ language plpgsql;
-- =========================================================================
-- 测试用例：
-- ----------
-- select compat_tools.f_unit_test('f_version_compare (''1.1.1'', ''2.0.0'')', '-1') into l_result;
-- select compat_tools.f_unit_test('f_version_compare (''2.1.1'', ''2.0.0'')', '1') into l_result;
-- select compat_tools.f_unit_test('f_version_compare (''1.2.3'', ''1.2.3'')', '0') into l_result;
-- select compat_tools.f_unit_test('f_version_compare (''1.2.3'', ''1.12.3'')', '-1') into l_result;
-- select compat_tools.f_unit_test('f_version_compare (''1.2.3.4'', ''1.2.3'')', '1') into l_result;
-- select compat_tools.f_unit_test('f_version_compare (''1.2.3'', ''1.2.3.4'')', '-1') into l_result;
-- select compat_tools.f_unit_test('f_version_compare (''1.2...3'', ''1.2.3.4'')', 'NULL', 'IS') into l_result;
-- select compat_tools.f_unit_test('f_version_compare (null, ''1.2.3.4'')', 'NULL', 'IS') into l_result;
-- select * from compat_tools.compat_testing where test_expr like 'f_version_compare%';
-- =========================================================================



-- =============================================================================
-- All creations are running in ANONYMOUS BLOCK
-- =============================================================================
do $PKG_CREATION$
declare
    l_cnt    bigint;
    l_result text;
begin
    -- =========================================================================
    -- dbms_metadata.get_ddl(varchar,varchar,varchar,varchar,varchar,varchar,bool)
    -- =========================================================================
    -- 变更历史：
    --     2022-05-06 [2.0] 增加 package, package_body 类型对象的 DDL 语句获取
    --     2022-05-06 [2.0] 增加对 2.1 以及之后版本中的 large sequence 的支持
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('FUNCTION', 'dbms_metadata', 'get_ddl(varchar,varchar,varchar,varchar,varchar,varchar,bool)', '2.0')
        then
            CREATE or replace FUNCTION dbms_metadata.get_ddl( object_type  VARCHAR
                                                            , name         VARCHAR
                                                            , schema       VARCHAR  DEFAULT  current_schema()
                                                            , version      VARCHAR  DEFAULT  'COMPATIBLE'  -- 暂不使用
                                                            , model        VARCHAR  DEFAULT  'ORACLE'      -- 暂不使用
                                                            , transform    VARCHAR  DEFAULT  'DDL'         -- 暂不使用
                                                            , debug        BOOLEAN  DEFAULT  'false')
            RETURNS text IMMUTABLE AS $$
            declare
                l_oid           text;
                l_temp_result   text;
                l_temp_record   record;
                l_ddl           text;
            begin
                -- 纯大写的参数转为全小写
                if upper(object_type) = object_type
                then
                    object_type := lower(object_type);
                end if;
                if upper(name) = name
                then
                    name := lower(name);
                end if;
                if upper(schema) = schema
                then
                    schema := lower(schema);
                end if;

                -- 按照对象类型生成 DDL
                begin
                    if object_type = 'table'
                    then
                        select pg_get_tabledef(oid) into l_ddl
                          from pg_class
                         where relname = name
                           and relnamespace = (select oid
                                                 from pg_namespace
                                                where nspname = schema)
                           and relkind = 'r';
                    elsif object_type = 'view'
                    then
                        select 'CREATE OR REPLACE VIEW "'||schema||'"."'||name||'" as '
                            || pg_get_viewdef(oid, true) into l_ddl
                          from pg_class
                         where relname = name
                           and relnamespace = (select oid
                                                 from pg_namespace
                                                where nspname = schema)
                           and relkind = 'v';
                    elsif object_type = 'index'
                    then
                        select pg_get_indexdef(oid, 0, true) into l_ddl
                          from pg_class
                         where relname = name
                           and relnamespace = (select oid
                                                 from pg_namespace
                                                where nspname = schema)
                           and relkind in ('i', 'I');
                    elsif object_type in ('constraint', 'ref_constraint')
                    then
                        select 'ALTER TABLE "'||schema||'"."'||cls.relname||'" ADD CONSTRAINT "'||name||'" '
                            || pg_get_constraintdef(con.oid, true) into l_ddl
                          from pg_constraint con
                          join pg_class cls on con.conrelid = cls.oid
                          join pg_namespace nsp on con.connamespace = nsp.oid
                         where con.conname = name
                           and nsp.nspname = schema;
                    elsif object_type = 'function'
                    then
                        select (pg_get_functiondef(oid)).definition into l_ddl
                          from pg_proc
                         where proname = name
                           and pronamespace = (select oid
                                                 from pg_namespace
                                                where nspname = schema)
                           and prokind != 'p';
                    elsif object_type = 'procedure'
                    then
                        select (pg_get_functiondef(oid)).definition into l_ddl
                          from pg_proc
                         where proname = name
                           and pronamespace = (select oid
                                                 from pg_namespace
                                                where nspname = schema)
                           and prokind = 'p';
                    elsif object_type = 'trigger'
                    then
                        select pg_get_triggerdef(t.oid, true) into l_ddl
                          from pg_trigger as t
                          join pg_class as c on t.tgrelid = c.oid
                          join pg_namespace as n on c.relnamespace = n.oid
                         where t.tgname = name
                           and n.nspname = schema;
                    elsif object_type in ('materialized_view', 'materialized view')
                    then
                        select 'CREATE MATERIALIZED VIEW "'||schema||'"."'||name||'" as '
                            || pg_get_viewdef(oid, true) into l_ddl
                          from pg_class
                         where relname = name
                           and relnamespace = (select oid
                                                 from pg_namespace
                                                where nspname = schema)
                           and relkind = 'm';
                    elsif object_type = 'sequence'
                    then
                        -- 直接查询 Sequence, 会在切换查询 large sequence 与 sequence 的时候遇到如下报错：
                        --     NOTICE:  type of parameter 29 (bigint) does not match that when preparing the plan (int16)
                        --     NOTICE:  type of parameter 29 (int16) does not match that when preparing the plan (bigint)
                        for l_temp_record in execute 'select start_value::text
                                                           , increment_by::text
                                                           , max_value::text
                                                           , min_value::text
                                                           , cache_value::text
                                                           , case when max_value > 2^63 then 1 else 0 end as is_large
                                                           , is_cycled
                                                        from "'||schema||'"."'||name||'"'
                        -- for l_temp_record in select l.cache_value
                        --                           , p.start_value
                        --                           , p.minimum_value
                        --                           , p.maximum_value
                        --                           , p.increment
                        --                           , p.cycle_option
                        --                        from pg_catalog.pg_sequence_last_value(('"'||schema||'"."'||name||'"')::regclass) as l  -- 2.0 版本不存在这个函数
                        --                        join pg_catalog.pg_sequence_parameters(('"'||schema||'"."'||name||'"')::regclass) as p on 1 = 1
                        loop
                            -- 获取序列关联的 serial 字段表
                            select max('"'||c.relname||'"."'||a.attname||'"') into l_temp_result
                              from pg_catalog.pg_attrdef as d
                              join pg_class as c on d.adrelid = c.oid
                              join pg_attribute as a on d.adrelid = a.attrelid and d.adnum = a.attnum
                              join pg_namespace as n on c.relnamespace = n.oid
                             where d.adsrc = 'nextval('''||name||'''::regclass)'
                               and n.nspname = schema;

                            -- 拼接序列创建语句
                            l_ddl := 'CREATE '||(case l_temp_record.is_large when 1 then 'LARGE ' else '' end)||'SEQUENCE "'||schema||'"."'||name||'"'
                                  || ' INCREMENT BY '||l_temp_record.increment_by
                                  || ' MINVALUE '||l_temp_record.min_value
                                  || ' MAXVALUE '||l_temp_record.max_value
                                  || ' START WITH '|| l_temp_record.start_value
                                  || ' CACHE '|| l_temp_record.cache_value
                                  || case when l_temp_record.is_cycled then ' CYCLE' else ' NOCYCLE' end
                                  || case when l_temp_result is null then ';' else ' OWNED BY '||l_temp_result||';' end;
                            -- l_ddl := 'CREATE '||(case when l_temp_record.maximum_value > 2^63 then 'LARGE ' else '' end)||'SEQUENCE "'||schema||'"."'||name||'"'
                            --       || ' INCREMENT BY '||l_temp_record.increment
                            --       || ' MINVALUE '||l_temp_record.minimum_value
                            --       || ' MAXVALUE '||l_temp_record.maximum_value
                            --       || ' START WITH '|| l_temp_record.start_value
                            --       || ' CACHE '|| l_temp_record.cache_value
                            --       || case when l_temp_record.cycle_option then ' CYCLE' else ' NOCYCLE' end
                            --       || case when l_temp_result is null then ';' else ' OWNED BY '||l_temp_result||';' end;
                        end loop;
                    elsif object_type = 'synonym'
                    then
                        select 'CREATE OR REPLACE SYNONYM "'||schema||'"."'||name||'" FOR "'||synobjschema||'"."'||synobjname||'";'
                          into l_ddl
                          from pg_synonym
                         where synname = name
                           and synnamespace = (select oid
                                                 from pg_namespace
                                                where nspname = schema);
                    elsif object_type = 'package'
                    then
                        select 'CREATE OR REPLACE PACKAGE "'||schema||'"."'|| name ||'"'
                            || (case when pkgsecdef then ' AUTHID DEFINER' else '' end)
                            || ' is '
                            || replace(pkgspecsrc, 'PACKAGE  DECLARE', '')
                            || ' ' || name ||';' into l_ddl
                          from gs_package
                         where pkgname = name
                           and pkgnamespace = (select oid
                                                 from pg_namespace
                                                where nspname = schema);
                    elsif object_type in ('package_body', 'package body')
                    then
                        select 'CREATE OR REPLACE PACKAGE BODY "'||schema||'"."'|| name ||'"'
                            || ' is '
                            || chr(10)
                            || regexp_replace(pkgbodydeclsrc, '(^\s*PACKAGE\s*DECLARE|\s*END\s*$)', '', 'g')
                            || chr(10)
                            || (case when pkgbodyinitsrc is null then 'END' else trim(replace(pkgbodyinitsrc, 'INSTANTIATION', ''), chr(10)||' ') end)
                            || ' ' || name ||';' into l_ddl
                          from gs_package
                         where pkgname = name
                           and pkgnamespace = (select oid
                                                 from pg_namespace
                                                where nspname = schema)
                           and pkgbodydeclsrc is not null;
                    else
                        raise warning 'GET_DDL for type [%] does not supported yet', object_type;
                    end if;
                exception
                    when others then
                        if debug
                        then
                            raise notice '%', SQLERRM;  -- 调试用途
                        end if;
                        raise exception '%', initcap(replace(object_type, '_', ' '))||' "'||schema||'"."'||name||'" does not exist !';
                end;

                return l_ddl;
            end;
            $$ LANGUAGE plpgsql;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_metadata.get_ddl(varchar,varchar,varchar,varchar,varchar,varchar): %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- dbms_output.enable(int4)
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('PROCEDURE', 'dbms_output', 'enable(int4)', '1.0')
        then
            execute 'CREATE OR REPLACE PROCEDURE dbms_output.enable(buffer_size int default 2000) AS
            BEGIN
                -- 创建临时表，用于缓存输出结果
                CREATE temporary table if not exists dbms_output_buffer
                (msg_content  text)
                ON COMMIT PRESERVE ROWS;
                INSERT INTO dbms_output_buffer values ('''');
            END;';
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_output.enable(buffer_size int default 2000): %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- dbms_output.disable()
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('PROCEDURE', 'dbms_output', 'disable()', '1.1')
        then
            execute 'CREATE OR REPLACE PROCEDURE dbms_output.disable() AS
            BEGIN
                for x in select count(*) cnt
                           from pg_class
                          where relname = ''dbms_output_buffer''
                            and relpersistence = ''t''
                            and relnamespace = pg_my_temp_schema()
                         having count(*) >= 1
                loop
                    drop table dbms_output_buffer;
                end loop;
            END;';
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_output.disable(): %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- dbms_output.get_line(text,int4)
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('PROCEDURE', 'dbms_output', 'get_line(text,int4)', '1.1')
        then
            execute '
            CREATE OR REPLACE PROCEDURE dbms_output.get_line ( line    OUT text
                                                             , status  OUT INTEGER) AS
            DECLARE
                l_cnt     bigint;
            BEGIN
                select count(*) into l_cnt
                  from pg_class
                 where relname = ''dbms_output_buffer''
                   and relpersistence = ''t''
                   and relnamespace = pg_my_temp_schema();

                if l_cnt = 0  -- 缓存数据表不存在
                then
                    line := '''';
                    status := 1;
                else
                    select case when instr(msg_content, chr(10)) = 0 then msg_content
                                else substr(msg_content, 1, instr(msg_content, chr(10)) - 1)
                           end into line
                      from dbms_output_buffer
                     limit 1;

                    if line is null or line = ''''
                    then
                        status := 1;
                    else
                        status := 0;
                    end if;

                    -- 丢弃剩余数据，刷新缓存
                    truncate table dbms_output_buffer;
                    insert into dbms_output_buffer values ('''');
                end if;
            END;';
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_output.get_line(text,int4): %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- dbms_output.get_lines(text,int4)
    -- CREATE temporary table if not exists dbms_output_buffer (msg_content  text) ON COMMIT PRESERVE ROWS;
    -- insert into dbms_output_buffer values ('');
    -- update dbms_output_buffer set msg_content = '123'||chr(10)||'abc'||chr(10)||'xxx'||chr(10);
    -- select lines, lines[1], lines[2], lines[2], array_length(lines, 1), numlines from dbms_output.get_lines (2);
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('PROCEDURE', 'dbms_output', 'get_lines(text[],int4)', '2.1')
        then
            execute '
            CREATE OR REPLACE PROCEDURE dbms_output.get_lines ( lines     OUT    text[]
                                                              , numlines  IN OUT INTEGER) AS
            DECLARE
                l_cnt        bigint;
                l_buffer     text;
            BEGIN
                select count(*) into l_cnt
                  from pg_class
                 where relname = ''dbms_output_buffer''
                   and relpersistence = ''t''
                   and relnamespace = pg_my_temp_schema();

                if l_cnt = 0  -- 缓存数据表存在
                then
                    lines := ''{}''::text[];
                    numlines := 0;
                else
                    select (string_to_array( case when instr(msg_content, chr(10), 1, numlines) = 0 then msg_content
                                                  else substr(msg_content, 1, instr(msg_content, chr(10), 1, numlines) - 1)
                                             end
                                           , chr(10)
                                           )
                           ) into lines
                      from dbms_output_buffer
                     limit 1;

                    numlines := coalesce(array_length(lines, 1), 0);

                    -- if lines is null or lines = ''''
                    -- then
                    --     numlines := 0;
                    -- else
                    --     numlines := length(lines) - length(replace(lines, chr(10), ''''));
                    --     -- 不是一换行结尾，行数 +1 （最后一个无换行符的行，也作为一行内容）
                    --     if substr(lines, length(lines), 1) != chr(10)
                    --     then
                    --         numlines := numlines + 1;
                    --     end if;
                    -- end if;

                    -- 丢弃剩余数据，刷新缓存
                    truncate table dbms_output_buffer;
                    insert into dbms_output_buffer values ('''');
                end if;
            END;';
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_output.get_lines(text[],int4): %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- dbms_output.new_line()
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('PROCEDURE', 'dbms_output', 'new_line()', '1.1')
        then
            execute '
            CREATE OR REPLACE PROCEDURE dbms_output.new_line() AS
            DECLARE
                l_cnt        bigint;
            BEGIN
                select count(*) into l_cnt
                  from pg_class
                 where relname = ''dbms_output_buffer''
                   and relpersistence = ''t''
                   and relnamespace = pg_my_temp_schema();

                if l_cnt > 0
                then
                    update dbms_output_buffer
                       set msg_content = msg_content||chr(10);
                end if;
            END;';
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_output.new_line(): %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- dbms_output.put(text)
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('PROCEDURE', 'dbms_output', 'put(text)', '1.2')
        then
            execute '
            CREATE OR REPLACE PROCEDURE dbms_output.put(item text) AS
            DECLARE
                l_cnt        bigint;
            BEGIN
                select count(*) into l_cnt
                  from pg_class
                 where relname = ''dbms_output_buffer''
                   and relpersistence = ''t''
                   and relnamespace = pg_my_temp_schema();

                if l_cnt > 0
                then
                    update dbms_output_buffer
                       set msg_content = msg_content||item;
                end if;

                raise notice ''%'', item;
            END;';
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_output.put(text): %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- dbms_output.put_line(text)
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('PROCEDURE', 'dbms_output', 'put_line(text)', '1.2')
        then
            execute '
            CREATE OR REPLACE PROCEDURE dbms_output.put_line(item text) AS
            DECLARE
                l_cnt        bigint;
            BEGIN
                select count(*) into l_cnt
                  from pg_class
                 where relname = ''dbms_output_buffer''
                   and relpersistence = ''t''
                   and relnamespace = pg_my_temp_schema();

                if l_cnt > 0
                then
                    update dbms_output_buffer
                       set msg_content = msg_content||item||chr(10);
                end if;

                raise notice ''%'', item;
            END;';
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_output.put_line(text): %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- dbms_random.initialize(int4)
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('PROCEDURE', 'dbms_random', 'initialize(int4)', '1.0')
        then
            execute '
            CREATE OR REPLACE PROCEDURE dbms_random.initialize(val int) AS
            BEGIN
                raise notice ''This procedure is deprecated with Release 11gR1, it should not be used.'';
                raise notice ''Nothing will be done!'';
            END;';
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_random.initialize(int4): %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- dbms_random.normal()
    -- 利用 Box-Muller 变换算法通过两个平均分布的随机数生成正态分布的随机数（效率不高）
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('FUNCTION', 'dbms_random', 'normal()', '1.0', 'sql')
        then
            execute '
            CREATE OR REPLACE FUNCTION dbms_random.normal()
            returns numeric AS $$
            select (sqrt(-2 * ln(random())) * cos(2 * pi() * random()))::numeric;
            $$ language sql;';
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_random.normal(): %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- dbms_random.random()
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('FUNCTION', 'dbms_random', 'random()', '1.0')
        then
            execute '
            CREATE OR REPLACE FUNCTION dbms_random.random()
            returns int AS $$
            BEGIN
                raise notice ''This function is deprecated with Release 11gR1, although currently supported, it should not be used.'';
                return case when random() >= 0.5 then power(2,31) * random() else 0 - power(2,31) * random() end;
            END;
            $$ language plpgsql';
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_random.random(): %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- dbms_random.seed(int4)
    -- PACKAGE 参数表明支持重载
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('PROCEDURE', 'dbms_random', 'seed(int4)', '1.0')
        then
            execute '
            CREATE OR REPLACE PROCEDURE dbms_random.seed(val int) PACKAGE AS
            BEGIN
                setseed(val / power(2,31));
            END;';
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_random.seed(int4): %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- dbms_random.seed(text)
    -- PACKAGE 参数表明支持重载
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('PROCEDURE', 'dbms_random', 'seed(text)', '1.0')
        then
            execute '
            CREATE OR REPLACE PROCEDURE dbms_random.seed(val text) PACKAGE AS
            DECLARE
                l_ascii   numeric;
            BEGIN
                if val = '''' or val is null
                then
                    null;
                else
                    select sum(ascii(id)) / (127 * count(*))
                      into l_ascii
                      from unnest(string_to_array(val, '''')) as id;
                    if l_ascii <= 1
                    then
                        setseed(l_ascii);
                    end if;
                end if;
            END;';
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_random.seed(text): %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- dbms_random.string(text,int4)
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('FUNCTION', 'dbms_random', 'string(text,int4)', '1.0', 'sql')
        then
            execute '
            CREATE OR REPLACE FUNCTION dbms_random.string(opt  text,len  int4)
            returns text AS $$
            select case upper(opt)
            -- 全小写随机
            when ''L'' then (select string_agg(chr(id), '''') from (select 96 + ceil(26 * random()) as id from generate_series(1, len)))
            -- 大小写混合随机
            when ''A'' then (select string_agg((array[''A'',''B'',''C'',''D'',''E'',''F'',''G'',''H'',''I'',''J'',''K'',''L'',''M'',''N'',''O'',''P'',''Q'',''R'',''S'',''T'',''U'',''V'',''W'',''X'',''Y'',''Z'',''a'',''b'',''c'',''d'',''e'',''f'',''g'',''h'',''i'',''j'',''k'',''l'',''m'',''n'',''o'',''p'',''q'',''r'',''s'',''t'',''u'',''v'',''w'',''x'',''y'',''z''])[id], '''') from (select ceil(52 * random()) as id from generate_series(1, len)))
            -- 大小写+数字混合随机
            when ''X'' then (select string_agg((array[''0'',''1'',''2'',''3'',''4'',''5'',''6'',''7'',''8'',''9'',''A'',''B'',''C'',''D'',''E'',''F'',''G'',''H'',''I'',''J'',''K'',''L'',''M'',''N'',''O'',''P'',''Q'',''R'',''S'',''T'',''U'',''V'',''W'',''X'',''Y'',''Z'',''a'',''b'',''c'',''d'',''e'',''f'',''g'',''h'',''i'',''j'',''k'',''l'',''m'',''n'',''o'',''p'',''q'',''r'',''s'',''t'',''u'',''v'',''w'',''x'',''y'',''z''])[id], '''') from (select ceil(62 * random()) as id from generate_series(1, len)))
            -- 全可见字符随机
            when ''P'' then (select string_agg(chr(id), '''') from (select 31 + ceil(95 * random()) as id from generate_series(1, len)))
            -- 全大写随机
            else (select string_agg(chr(id), '''') from (select 64 + ceil(26 * random()) as id from generate_series(1, len)))
            end;
            $$ language sql';
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_random.string(text,int4): %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- dbms_random.terminate()
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('PROCEDURE', 'dbms_random', 'terminate()', '1.0')
        then
            execute '
            CREATE OR REPLACE PROCEDURE dbms_random.terminate() AS
            BEGIN
                raise notice ''This procedure is deprecated with Release 11gR1, it should not be used.'';
                raise notice ''Nothing will be done!'';
            END;';
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_random.terminate(): %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- dbms_random.value()
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('FUNCTION', 'dbms_random', 'value()', '1.0', 'sql')
        then
            execute '
            CREATE OR REPLACE FUNCTION dbms_random.value()
            returns numeric AS $$
            select random()::numeric;
            $$ language sql;';
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_random.value(): %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- dbms_random.value(numeric,numeric)
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('FUNCTION', 'dbms_random', 'value(numeric,numeric)', '1.0', 'sql')
        then
            execute '
            CREATE OR REPLACE FUNCTION dbms_random.value(low  numeric, high  numeric)
            returns numeric AS $$
            select (random() * (high - low) + low)::numeric;
            $$ language sql;';
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_random.value(numeric,numeric): %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- dbms_job.broken(int8,bool,timestamp)
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('PROCEDURE', 'dbms_job', 'broken(int8,bool,timestamp)', '1.0')
        then
            execute '
            CREATE OR REPLACE PROCEDURE dbms_job.broken ( job       int8
                                                        , broken    bool
                                                        , next_date timestamp default sysdate)
            AS
            DECLARE
                l_result  text;
            BEGIN
                select pkg_service.job_finish(job, broken, next_date) into l_result;
            END;';
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_job.broken(int8,bool,timestamp): %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- dbms_job.change(int8,text,timestamp,text,int4,bool)
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('PROCEDURE', 'dbms_job', 'change(int8,text,timestamp,text,int4,bool)', '1.0')
        then
            execute '
            CREATE OR REPLACE PROCEDURE dbms_job.change ( job         int8
                                                        , what        text
                                                        , next_date   timestamp
                                                        , "interval"  text
                                                        , instance    int4      default null
                                                        , force       bool      default ''false'')
            AS
            DECLARE
                l_result  text;
            BEGIN
                if instance is not null
                then
                    raise notice ''Parameter "instance" does nothing in this procedure !'';
                end if;
                if force
                then
                    raise notice ''Parameter "force" does nothing in this procedure !'';
                end if;
                select pkg_service.job_update(job, next_date, interval, what) into l_result;
            END;';
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_job.change(int8,text,timestamp,text,int4,bool): %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- dbms_job.instance(int8,int4,bool)
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('PROCEDURE', 'dbms_job', 'instance(int8,int4,bool)', '1.0')
        then
            execute '
            CREATE OR REPLACE PROCEDURE dbms_job.instance ( job       int8
                                                          , instance  int4
                                                          , force     bool  default ''false'')
            AS
            BEGIN
                raise notice ''Nothing to do in this procedure !'';
            END;';
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_job.instance(int8,int4,bool): %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- dbms_job.interval(int8,text)
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('PROCEDURE', 'dbms_job', 'interval(int8,text)', '1.0')
        then
            execute '
            CREATE OR REPLACE PROCEDURE dbms_job.interval ( job           int8
                                                          , "interval"    text)
            AS
            DECLARE
                l_next_date     timestamp;
                l_what          text;
                l_result        text;
            BEGIN
                select next_run_date into l_next_date
                  from pg_job
                 where job_id = job;
                select what into l_what
                  from pg_job_proc
                 where job_id = job;
                select pkg_service.job_update(job, l_next_date, interval, l_what) into l_result;
            END;';
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_job.interval(int8,text): %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- dbms_job.next_date(int8,timestamp)
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('PROCEDURE', 'dbms_job', 'next_date(int8,timestamp)', '1.0')
        then
            execute '
            CREATE OR REPLACE PROCEDURE dbms_job.next_date ( job        int8
                                                           , next_date  timestamp)
            AS
            DECLARE
                l_interval    text;
                l_what        text;
                l_result      text;
            BEGIN
                select interval into l_interval
                  from pg_job
                 where job_id = job;
                select what into l_what
                  from pg_job_proc
                 where job_id = job;
                select pkg_service.job_update(job, next_date, l_interval, l_what) into l_result;
            END;';
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_job.next_date(int8,timestamp): %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- dbms_job.remove(int8)
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('PROCEDURE', 'dbms_job', 'remove(int8)', '1.0')
        then
            execute '
            CREATE OR REPLACE PROCEDURE dbms_job.remove(job int8)
            AS
            DECLARE
                l_result  text;
            BEGIN
                select pkg_service.job_cancel(job) into l_result;
            END;';
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_job.remove(int8): %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- dbms_job.run(int8,bool)
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('PROCEDURE', 'dbms_job', 'run(int8,bool)', '1.0')
        then
            execute '
            CREATE OR REPLACE PROCEDURE dbms_job.run( job    int8
                                                    , force  boolean default ''false'')
            AS
            DECLARE
                l_interval    text;
                l_next_date   timestamp;
                l_what        text;
                l_result      text;
            BEGIN
                if force
                then
                    raise notice ''Parameter "force" does nothing in this procedure !'';
                end if;
                select interval into l_interval
                  from pg_job
                 where job_id = job;
                select what into l_what
                  from pg_job_proc
                 where job_id = job;
                execute immediate ''select ''||l_interval into l_next_date;
                execute immediate l_what;
                select pkg_service.job_update(job, l_next_date, l_interval, l_what) into l_result;
            END;';
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_job.run(int8,bool): %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- dbms_job.submit(int8,text,timestamp,text,bool,int4,bool)
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('PROCEDURE', 'dbms_job', 'submit(int8,text,timestamp,text,bool,int4,bool)', '1.0')
        then
            execute '
            CREATE OR REPLACE PROCEDURE dbms_job.submit ( job         out int8
                                                        , what        in  text
                                                        , next_date   in  timestamp default sysdate
                                                        , "interval"  in  text      default ''null''
                                                        , no_parse    in  bool      default ''false''
                                                        , instance    in  int4      default null
                                                        , force       in  bool      default ''false'')
            AS
            BEGIN
                if no_parse
                then
                    raise notice ''Parameter "no_parse" does nothing in this procedure !'';
                end if;
                if instance is not null
                then
                    raise notice ''Parameter "instance" does nothing in this procedure !'';
                end if;
                if force
                then
                    raise notice ''Parameter "force" does nothing in this procedure !'';
                end if;
                job := pkg_service.job_submit(null, what, next_date, interval);
            END;';
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_job.submit(int8,text,timestamp,text,bool,int4,bool): %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- dbms_job.user_export(int8,text)
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('PROCEDURE', 'dbms_job', 'user_export(int8,text)', '1.0')
        then
            execute '
            CREATE OR REPLACE PROCEDURE dbms_job.user_export ( job    in     int8
                                                             , mycall inout  text)
            AS
            BEGIN
                select ''select pkg_service.job_submit(id=>''
                    || j.job_id
                    || '', content=>''
                    || chr(39) || p.what || chr(39)
                    || '', next_time=>to_timestamp('' || chr(39) || to_char(next_run_date, ''yyyy-mm-dd hh24:mi:ss'') || chr(39)
                                                 || '', '' || chr(39) || ''yyyy-mm-dd hh24:mi:ss'' || chr(39)
                    || ''), interval_time=>''||chr(39)||interval||chr(39)||'');''
                  into mycall
                  from pg_job as j
                  join pg_job_proc as p on j.job_id = p.job_id
                 where j.job_id = job;
            END;';
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_job.user_export(int8,text): %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- dbms_job.what(int8,text)
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('PROCEDURE', 'dbms_job', 'what(int8,text)', '1.0')
        then
            execute '
            CREATE OR REPLACE PROCEDURE dbms_job.what ( job   int8
                                                      , what  text)
            AS
            DECLARE
                l_next_date     timestamp;
                l_interval      text;
                l_result        text;
            BEGIN
                select next_run_date, interval
                  into l_next_date, l_interval
                  from pg_job
                 where job_id = job;
                select pkg_service.job_update(job, l_next_date, l_interval, what) into l_result;
            END;';
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_job.what(int8,text): %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- dbms_lock.sleep(float8)
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('PROCEDURE', 'dbms_lock', 'sleep(float8)', '1.0')
        then
            execute '
            CREATE OR REPLACE PROCEDURE dbms_lock.sleep(pi_seconds  float8)
            AS
            DECLARE
                l_result  text;
            BEGIN
                select pg_sleep(pi_seconds) into l_result;
            END;';
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_lock.sleep(float8): %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- dbms_application_info.read_client_info(text)
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('PROCEDURE', 'dbms_application_info', 'read_client_info(text)', '1.0')
        then
            execute '
            CREATE OR REPLACE PROCEDURE dbms_application_info.read_client_info(client_info  out  text)
            AS
            BEGIN
                select setting
                  into client_info
                  from pg_settings
                 where name = ''application_name'';
            END;';
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_application_info.read_client_info(text): %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- dbms_application_info.set_client_info(text)
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('PROCEDURE', 'dbms_application_info', 'set_client_info(text)', '1.0')
        then
            execute '
            CREATE OR REPLACE PROCEDURE dbms_application_info.set_client_info(client_info  text)
            AS
            BEGIN
                execute immediate ''alter session set application_name = ''''''||client_info||'''''''';
            END;';
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_application_info.set_client_info(text): %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- dbms_application_info.read_module(text,text)
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('PROCEDURE', 'dbms_application_info', 'read_module(text,text)', '1.0')
        then
            execute '
            CREATE OR REPLACE PROCEDURE dbms_application_info.read_module( module_name  out  text
                                                                         , action_name  out  text)
            AS
            BEGIN
                select setting, null
                  into module_name, action_name
                  from pg_settings
                 where name = ''application_name'';
            END;';
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_application_info.read_module(text,text): %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- dbms_application_info.set_module(text,text)
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('PROCEDURE', 'dbms_application_info', 'set_module(text,text)', '1.0')
        then
            execute '
            CREATE OR REPLACE PROCEDURE dbms_application_info.set_module( module_name  text
                                                                        , action_name  text)
            AS
            BEGIN
                execute immediate ''alter session set application_name = ''''''||module_name||'''''''';
            END;';
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_application_info.set_module(text,text): %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- dbms_utility.canonicalize(text,text,int4)
    -- =========================================================================
    -- 变更历史
    --    2022-06-10  1.1  修复双引号中多个点号情况下，结果不正确的问题
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('PROCEDURE', 'dbms_utility', 'canonicalize(text,text,int4)', '1.1')
        then
            execute '
            CREATE OR REPLACE PROCEDURE dbms_utility.canonicalize( name        IN   text
                                                                 , canon_name  OUT  text
                                                                 , canon_len   IN   int4)
            AS
            DECLARE
                l_result        text := '''';
                l_quota_tips    int  := 0;
                l_cnt           int  := 0;
            BEGIN
                canon_name := '''';

                -- for x in select split_part, count(*) over() as cnt
                --            from (select trim(case when t.x ~ ''^"'' and t.x !~ ''"$'' and lead(t.x) over() ~ ''"$'' and lead(t.x) over() !~ ''^"'' then chr(27)  -- ESC
                --                                   when t.x ~ ''"$'' and t.x !~ ''^"'' and lag(t.x) over() ~ ''^"'' and lag(t.x) over() !~ ''"$'' then lag(t.x) over() || ''.'' || t.x
                --                                   else t.x end, chr(9)||chr(10)||chr(11)||chr(12)||chr(13)||'' '') as split_part
                --                    from (select regexp_split_to_table(name, ''\.'') as x) as t)
                --           where split_part != chr(27)

                for x in select x as x_data
                              , case when x ~ ''^\s*"'' and x !~ ''"\s*$'' then -1
                                     when x ~ ''"\s*$'' and x !~ ''^\s*"'' then 1
                                     else 0
                                end as x_tip
                              , lead(rownum) over () as is_last
                           from (select regexp_split_to_table(name, ''\.'') as x)
                loop
                    l_quota_tips := l_quota_tips + x.x_tip;
                    -- raise notice ''[%] [%] [%] [%]'', x.x_data, x.x_tip, x.is_last, x.cnt;
                    -- 单独出现的右括号，前面没有发现左括号
                    if l_quota_tips = 1
                    then
                        raise exception ''missing period (only right quotation)'';
                    -- 左右括号匹配上了，或者没有完全没有使用括号，则处理当前部分的数据
                    -- 或者处理到最后一条记录了: x.is_last is null
                    elsif l_quota_tips = 0 or x.is_last is null
                    then
                        l_cnt := l_cnt + 1;
                        -- 处理数据
                        if l_quota_tips = 0 and x.x_tip = 0    -- 完全没有使用括号
                        then
                            l_result := trim(x.x_data, chr(9)||chr(10)||chr(11)||chr(12)||chr(13)||'' '');  -- 移除首尾的空字符
                        else
                            l_result := trim(l_result||''.''||x.x_data, chr(9)||chr(10)||chr(11)||chr(12)||chr(13)||'' '');  -- 移除首尾的空字符
                        end if;
                        -- raise notice ''Process [%]'', l_result;

                        -- 0. 异常情况： 双引号中有双引号 ("Hon"gye")，双引号只包裹部分内容 (hon"gye")，非双引号时单词为空等 (missing period)，
                        if l_result is null or l_result = '''' or l_result ~ ''.".''
                        then
                            raise exception ''missing period (empty, double quotation)'';
                        -- 双引号空字符串
                        elsif l_result = ''""''
                        then
                            raise exception ''illegal zero-length identifier'';
                        -- 双引号单词
                        elsif l_result ~ ''^".+"$''
                        then
                            if l_cnt = 1 and x.is_last is null
                            then
                                canon_name := substr(l_result, 2, length(l_result) - 2);
                            elsif canon_name is null or canon_name = ''''
                            then
                                canon_name := l_result;
                            else
                                canon_name := canon_name || ''.'' || l_result;
                            end if;
                        -- 以下为非双引号单词
                        -- 异常情况： 非双引号时单词中间有空格，制表符，换行等 (missing period)，
                        elsif l_result ~ ''\s+''
                        then
                            raise exception ''missing period (blank, \t, \n)'';
                        -- 非双引号中有不合法的符号 (unexpected name string ""a".b,c.f"),
                        elsif l_result !~ ''^[\w\$\#]+$''
                        then
                            raise exception ''unexpected name string "%"'', name;
                        elsif l_cnt = 1 and x.is_last is null
                        then
                            canon_name := upper(l_result);
                        elsif canon_name is null or canon_name = ''''
                        then
                            canon_name := ''"'' || upper(l_result) || ''"'';
                        else
                            canon_name := canon_name || ''."'' || upper(l_result) || ''"'';
                        end if;

                        -- 初始化临时变量，准备下一个部分的数据处理
                        l_quota_tips := 0;
                        l_result := '''';
                    -- l_quota_tips 为负数，则需要依次往后查找对应的有括号，使得 x_tip 累加 (l_quota_tips) 为 0
                    else
                        if x.x_tip = -1 and l_quota_tips = -1  -- 遇到第一个左括号
                        then
                            l_result := x.x_data;
                        else
                            l_result := l_result||''.''||x.x_data;
                        end if;
                        -- raise notice ''Continue [%]'', l_result;
                    end if;
                end loop;

                canon_name := substrb(canon_name, 1, canon_len);
            END;';
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_utility.canonicalize(text,text,int4): %', SQLERRM;
            rollback;
    end;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('dbms_utility.canonicalize(''HongYe'', 100)', '''HONGYE''') into l_result;
    select compat_tools.f_unit_test('dbms_utility.canonicalize(''"HongYe"'', 100)', '''HongYe''') into l_result;
    select compat_tools.f_unit_test('dbms_utility.canonicalize(''HongYe '', 100)', '''HONGYE''') into l_result;
    select compat_tools.f_unit_test('dbms_utility.canonicalize(''"HongYe "'', 100)', '''HongYe ''') into l_result;
    select compat_tools.f_unit_test('dbms_utility.canonicalize(''HongYe$'', 100)', '''HONGYE$''') into l_result;
    select compat_tools.f_unit_test('dbms_utility.canonicalize(''"HongYe$"'', 100)', '''HongYe$''') into l_result;
    select compat_tools.f_unit_test('dbms_utility.canonicalize(''HongYe$''||chr(9), 100)', '''HONGYE$''') into l_result;
    select compat_tools.f_unit_test('dbms_utility.canonicalize(''"HongYe$''||chr(9)||''"'', 100)', '''HongYe$'||chr(9)||'''') into l_result;
    select compat_tools.f_unit_test('dbms_utility.canonicalize(''HongYe$''||chr(10), 100)', '''HONGYE$''') into l_result;
    select compat_tools.f_unit_test('dbms_utility.canonicalize(''"HongYe$''||chr(10)||''"'', 100)', '''HongYe$'||chr(10)||'''') into l_result;
    select compat_tools.f_unit_test('dbms_utility.canonicalize(''HongYe.tab'', 100)', '''"HONGYE"."TAB"''') into l_result;
    select compat_tools.f_unit_test('dbms_utility.canonicalize(''HongYe. tab '', 100)', '''"HONGYE"."TAB"''') into l_result;
    select compat_tools.f_unit_test('dbms_utility.canonicalize(''"HongYe".tab'', 100)', '''"HongYe"."TAB"''') into l_result;
    select compat_tools.f_unit_test('dbms_utility.canonicalize(''HongYe."tab"'', 100)', '''"HONGYE"."tab"''') into l_result;
    select compat_tools.f_unit_test('dbms_utility.canonicalize(''"HongYe"."tab"'', 100)', '''"HongYe"."tab"''') into l_result;
    select compat_tools.f_unit_test('dbms_utility.canonicalize(''"Hon gYe"."ta b"'', 100)', '''"Hon gYe"."ta b"''') into l_result;
    select compat_tools.f_unit_test('dbms_utility.canonicalize(''"HongYe'||chr(9)||'"."t'||chr(10)||'ab"'', 100)', '''"HongYe'||chr(9)||'"."t'||chr(10)||'ab"''') into l_result;
    select compat_tools.f_unit_test('dbms_utility.canonicalize(''HongYe.pkg.proc'', 100)', '''"HONGYE"."PKG"."PROC"''') into l_result;
    select compat_tools.f_unit_test('dbms_utility.canonicalize(''"Hong.Ye".Pkg."Pr oc"'', 100)', '''"Hong.Ye"."PKG"."Pr oc"''') into l_result;
    select compat_tools.f_unit_test('dbms_utility.canonicalize(''"Hong.Ye".Pkg."Pr oc"'', 10)', '''"Hong.Ye".''') into l_result;
    select compat_tools.f_unit_test('dbms_utility.canonicalize(''"abc". "abc.de"."aaa.xxx.'', 100)', '''P0001: unexpected name string ""abc". "abc.de"."aaa.xxx."''') into l_result;
    select compat_tools.f_unit_test('dbms_utility.canonicalize(''"abc". "abc.de"."aaa.xxx."'', 100)', '''"abc"."abc.de"."aaa.xxx."''') into l_result;
    select compat_tools.f_unit_test('dbms_utility.canonicalize(''"abc.def.ghi"'', 100)', '''abc.def.ghi''') into l_result;

    -- 测试备注：
    --     没有点号的情况下：
    --         0. 异常情况： 双引号中有双引号 ("Hon"gye")，双引号只包裹部分内容 (hon"gye")，非双引号时单词中间有空格，制表符，换行，或者单词为空等 (missing period)，
    --                      非双引号中有不合法的符号 (unexpected name string ""a".b,c.f"),
    --                      双引号空字符串 ()
    --         1. 有双引号包裹： 保留双引号中的内容，移除首尾双引号
    --         2. 没有双引号包裹： 全转大写，移除首尾空格，制表符，换行等
    --     有点号的情况：
    --         1. 不论有没有双引号包括，结果的各个部分都有双引号包裹
    --         2. 按点号切割，每个部分按照没有点号的逻辑处理

    -- declare
    --   l_out    varchar2(100);
    -- begin
    --   for x in (select rownum as id, x
    --               from (select 'HongYe' as x from dual union all
    --                     select '"HongYe"' as x from dual union all
    --                     select 'HongYe ' as x from dual union all
    --                     select '"HongYe "' as x from dual union all
    --                     select 'HongYe$' as x from dual union all
    --                     select '"HongYe$"' as x from dual union all
    --                     select 'HongYe$'||chr(9) as x from dual union all
    --                     select '"HongYe$"'||chr(9) as x from dual union all
    --                     select 'HongYe$'||chr(10) as x from dual union all
    --                     select '"HongYe$'||chr(10)||'"' as x from dual union all
    --                     select 'HongYe.tab' as x from dual union all
    --                     select 'HongYe. tab ' as x from dual union all
    --                     select '"HongYe".tab' as x from dual union all
    --                     select 'HongYe."tab"' as x from dual union all
    --                     select '"HongYe"."tab"' as x from dual union all
    --                     select '"Hon gYe"."ta b"' as x from dual union all
    --                     select '"HongYe'||chr(9)||'"."t'||chr(10)||'ab"' as x from dual union all
    --                     select 'HongYe.pkg.proc' as x from dual union all
    --                     select '"Hong.Ye".Pkg."Pr oc"' as x from dual
    --            ))
    --   loop
    --     dbms_utility.canonicalize(x.x, l_out, 100);
    --     dbms_output.put_line(x.id||'. ['||x.x||'] => ['||l_out||']');
    --   end loop;
    -- end;
    -- /
    -- =========================================================================


    -- =========================================================================
    -- dbms_utility.comma_to_table(text,int4,text[])
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('PROCEDURE', 'dbms_utility', 'comma_to_table(text,int4,text[])', '1.0')
        then
            execute '
            CREATE OR REPLACE PROCEDURE dbms_utility.comma_to_table( list        IN   text
                                                                   , tablen     OUT   int4
                                                                   , tab        OUT   text[])
            AS
            DECLARE
                l_result   text;
            BEGIN
                tablen := 0;
                tab := ''{}''::text[];
                for x in select split_part, count(*) over() as cnt, rownum as row_id
                           from (select case when t.x ~ ''^\s*"'' and t.x !~ ''"\s*$'' and lead(t.x) over() ~ ''"\s*$'' and lead(t.x) over() !~ ''^\s*"'' then chr(27)  -- ESC
                                             when t.x ~ ''"\s*$'' and t.x !~ ''^\s*"'' and lag(t.x) over() ~ ''^\s*"'' and lag(t.x) over() !~ ''"\s*$'' then lag(t.x) over() || ''.'' || t.x
                                             else t.x
                                        end as split_part
                                   from (select regexp_split_to_table(list, '','') as x) as t)
                          where split_part != chr(27) or split_part is null
                loop
                    -- 0. 异常情况： 空串，空白字符串，字符串中间有双引号
                    if x.split_part is null or x.split_part ~ ''^\s+$'' or instr(trim(trim(x.split_part), ''"''), ''"'') > 0 or length(trim(trim(x.split_part), ''"'')) > 30
                    then
                        if x.cnt = 1
                        then
                            raise exception ''comma-separated list invalid near %'', x.split_part;
                        elsif x.row_id = 1
                        then
                            raise exception ''comma-separated list invalid near %,'', x.split_part;
                        elsif x.row_id = x.cnt
                        then
                            raise exception ''comma-separated list invalid near ,%'', x.split_part;
                        else
                            raise exception ''comma-separated list invalid near ,%,'', x.split_part;
                        end if;
                    else
                        tab := array_append(tab, x.split_part);
                        tablen := tablen + 1;
                    end if;
                end loop;
                tab := array_append(tab, NULL);
            END;';
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_utility.comma_to_table(text,int4,text[]): %', SQLERRM;
            rollback;
    end;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('(dbms_utility.comma_to_table(''hongye'')).tablen', '1') into l_result;
    select compat_tools.f_unit_test('(dbms_utility.comma_to_table(''HongYe, "HongYe","HongYe,12345678901234567890123" , HongYeasd'')).tablen', '4') into l_result;
    select compat_tools.f_unit_test('(dbms_utility.comma_to_table(''hongye'')).tab::text', '''{hongye,NULL}''') into l_result;
    select compat_tools.f_unit_test('(dbms_utility.comma_to_table(''HongYe, "HongYe","HongYe,12345678901234567890123" , HongYeasd'')).tab::text', '''{HongYe," \"HongYe\"","\"HongYe.12345678901234567890123\" "," HongYeasd",NULL}''') into l_result;

    -- Oracle 测试代码：
    -- declare
    --   l_tablen number;
    --   l_tab    dbms_utility.uncl_array;
    -- begin
    --   for x in (select rownum as id, x
    --               from (select 'HongYe' as x from dual union all
    --                     select 'HongYe, "HongYe","HongYe 12345678901234567890123" , HongYeasd' as x from dual
    --                    )
    --            )
    --   loop
    --     dbms_utility.comma_to_table(x.x, l_tablen, l_tab);
    --     dbms_output.put_line(x.id||'. ['||x.x||'] => ['||l_tablen||']');
    --     for y in 1..(l_tablen+1)
    --     loop
    --         dbms_output.put_line('   ['||l_tab(y)||']');
    --     end loop;
    --   end loop;
    -- end;
    -- /
    -- =========================================================================


    -- =========================================================================
    -- dbms_utility.table_to_comma(text[],int4,text)
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('PROCEDURE', 'dbms_utility', 'table_to_comma(text[],int4,text)', '1.0')
        then
            execute '
            CREATE OR REPLACE PROCEDURE dbms_utility.table_to_comma( tab         IN   text[]
                                                                   , tablen     OUT   int4
                                                                   , list       OUT   text)
            AS
            BEGIN
                tablen := array_length(tab, 1) - 1;
                list := trim(array_to_string(tab, '',''), '','');
            END;';
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('(dbms_utility.table_to_comma(''{HongYe,NULL}''::text[])).tablen', '1') into l_result;
    select compat_tools.f_unit_test('(dbms_utility.table_to_comma(''{HongYe,NULL}''::text[])).list', '''HongYe''') into l_result;
    select compat_tools.f_unit_test('(dbms_utility.table_to_comma(''{HongYe," \"HongYe\"","\"HongYe.12345678901234567890123\" "," HongYeasd",NULL}''::text[])).tablen', '4') into l_result;
    select compat_tools.f_unit_test('(dbms_utility.table_to_comma(''{HongYe," \"HongYe\"","\"HongYe.12345678901234567890123\" "," HongYeasd",NULL}''::text[])).list', '''HongYe, "HongYe","HongYe.12345678901234567890123" , HongYeasd''') into l_result;
    -- =========================================================================
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_utility.table_to_comma(text[],int4,text): %', SQLERRM;
            rollback;
    end;

    -- Oracle 测试代码：
    -- declare
    --   l_tablen number;
    --   l_tab    dbms_utility.uncl_array;
    --   l_list   varchar2(4000);
    -- begin
    --   for x in (select rownum as id, x
    --               from (select 'HongYe' as x from dual union all
    --                     select 'HongYe, "HongYe","HongYe 12345678901234567890123" , HongYeasd' as x from dual
    --                    )
    --            )
    --   loop
    --     dbms_utility.comma_to_table(x.x, l_tablen, l_tab);
    --     dbms_output.put_line(x.id||'. ['||x.x||'] => ['||l_tablen||']');
    --     for y in 1..(l_tablen+1)
    --     loop
    --         dbms_output.put_line('   ['||l_tab(y)||']');
    --     end loop;
    --     dbms_utility.table_to_comma(l_tab, l_tablen, l_list);
    --     dbms_output.put_line('----> ['||l_list||']');
    --   end loop;
    -- end;
    -- /
    -- =========================================================================


    -- =========================================================================
    -- dbms_utility.db_version(text,text)
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('PROCEDURE', 'dbms_utility', 'db_version(text,text)', '1.0')
        then
            execute '
            CREATE OR REPLACE PROCEDURE dbms_utility.db_version( version        OUT   text
                                                               , compatibility  OUT   text)
            AS
            BEGIN
                select trim(regexp_substr(version(), ''\([^\)]+\)''), ''()''), datcompatibility
                  into version, compatibility
                  from pg_database
                 where datname = current_database();
            END;';
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_utility.db_version(text,text): %', SQLERRM;
            rollback;
    end;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('(dbms_utility.db_version()).version', 'NULL', 'IS NOT') into l_result;
    select compat_tools.f_unit_test('(dbms_utility.db_version()).compatibility', '(''A'', ''B'', ''C'', ''PG'')', 'IN') into l_result;

    -- Oracle 测试代码：
    -- declare
    --     l_version varchar2(1000);
    --     l_comp    varchar2(1000);
    -- begin
    --     dbms_utility.db_version(l_version, l_comp);
    --     dbms_output.put_line('version: ['||l_version||']');
    --     dbms_output.put_line('compatibility: ['||l_comp||']');
    -- end;
    -- /
    -- version: [11.2.0.4.0]
    -- compatibility: [11.2.0.4.0]
    -- =========================================================================


    -- =========================================================================
    -- dbms_utility.exec_ddl_statement(text)
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('PROCEDURE', 'dbms_utility', 'exec_ddl_statement(text)', '1.0')
        then
            execute '
            CREATE OR REPLACE PROCEDURE dbms_utility.exec_ddl_statement(parse_string  text)
            AS
            BEGIN
                execute immediate parse_string;
            END;';
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_utility.exec_ddl_statement(text): %', SQLERRM;
            rollback;
    end;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    -- 注意： SQL语句中需要写明所有表对应的Schema，否则都对应到dbms_utility中了
    select compat_tools.f_unit_test('dbms_utility.exec_ddl_statement(''create table if not exists public.test_exec_ddl_stmt (id int, name text);'')', 'NOT NULL', 'IS') into l_result;
    select compat_tools.f_unit_test('dbms_utility.exec_ddl_statement('' truncate table public.test_exec_ddl_stmt'')', 'NOT NULL', 'IS') into l_result;
    select compat_tools.f_unit_test('dbms_utility.exec_ddl_statement(''drop table if exists public.test_exec_ddl_stmt ; '')', 'NOT NULL', 'IS') into l_result;

    -- Oracle 测试代码：
    -- begin
    --     dbms_utility.exec_ddl_statement('create table test_exec_ddl_stmt(id int)');
    -- end;
    -- /
    -- =========================================================================


    -- =========================================================================
    -- dbms_obfuscation_toolkit.md5(text)
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('PROCEDURE', 'dbms_obfuscation_toolkit', 'md5(text)', '1.0', 'sql')
        then
            CREATE OR REPLACE FUNCTION dbms_obfuscation_toolkit.md5(input_string  text)
            returns raw IMMUTABLE
            as $$
                select hextoraw(md5(input_string));
            $$ language sql;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_obfuscation_toolkit.md5(text): %', SQLERRM;
            rollback;
    end;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('DBMS_OBFUSCATION_TOOLKIT.MD5(INPUT_STRING => ''hongye'')::text', '''AAEC711EEC7B24EE17E456EBEDBF9FBE''') into l_result;
    select compat_tools.f_unit_test('rawtohex(DBMS_OBFUSCATION_TOOLKIT.MD5(INPUT_STRING => ''hongye''))::text', '''AAEC711EEC7B24EE17E456EBEDBF9FBE''') into l_result;

    -- Oracle 测试代码：
    -- SQL> select rawtohex(DBMS_OBFUSCATION_TOOLKIT.MD5(INPUT_STRING => 'hongye')) from dual;
    -- RAWTOHEX(DBMS_OBFUSCATION_TOOLKIT.MD5(INPUT_STRING=>'HONGYE'))
    -- --------------------------------------------------------------------------------
    -- AAEC711EEC7B24EE17E456EBEDBF9FBE
    -- =========================================================================


    -- -- =========================================================================
    -- -- dbms_obfuscation_toolkit.md5(raw)
    -- -- =========================================================================
    -- begin
    --     if compat_tools.drop_compat_package('PROCEDURE', 'dbms_obfuscation_toolkit', 'md5(raw)', '1.0')
    --     then
    --         CREATE OR REPLACE FUNCTION dbms_obfuscation_toolkit.md5(input  text)
    --         returns raw IMMUTABLE
    --         as $$
    --             select hextoraw(md5(input));
    --         $$ language sql;
    --     end if;
    --     commit;
    -- exception
    --     when others then
    --         RAISE WARNING 'Error in dbms_obfuscation_toolkit.md5(text): %', SQLERRM;
    --         rollback;
    -- end;
    -- -- =========================================================================
    -- -- 测试用例：
    -- -- ----------
    -- select compat_tools.f_unit_test('DBMS_OBFUSCATION_TOOLKIT.MD5(INPUT_STRING => ''hongye'')::text', '''AAEC711EEC7B24EE17E456EBEDBF9FBE''') into l_result;
    -- select compat_tools.f_unit_test('rawtohex(DBMS_OBFUSCATION_TOOLKIT.MD5(INPUT_STRING => ''hongye''))::text', '''AAEC711EEC7B24EE17E456EBEDBF9FBE''') into l_result;

    -- -- Oracle 测试代码：
    -- -- SQL> select DBMS_OBFUSCATION_TOOLKIT.MD5(INPUT => rawtohex('hongye')) from dual;
    -- -- DBMS_OBFUSCATION_TOOLKIT.MD5(INPUT=>RAWTOHEX('HONGYE'))
    -- -- --------------------------------------------------------------------------------
    -- -- AAEC711EEC7B24EE17E456EBEDBF9FBE
    -- -- =========================================================================


    -- =========================================================================
    -- utl_url
    -- 参考 :https://gitee.com/darkathena/opengauss-oracle/blob/main/oracle-package/utl_url.sql
    -- =========================================================================
    -- utl_url.escape(text,bool,text)
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('FUNCTION', 'utl_url', 'escape(text,bool,text)', '1.0')
        then
            execute $q$
            CREATE OR REPLACE FUNCTION UTL_URL.escape(url IN TEXT, escape_reserved_chars IN BOOL DEFAULT FALSE, url_charset IN TEXT DEFAULT 'UTF8')
            RETURNS TEXT
            LANGUAGE plpgsql
            IMMUTABLE NOT FENCED NOT SHIPPABLE
            AS $$
            declare
            L_TMP            TEXT DEFAULT '';
            L_BAD            TEXT DEFAULT ' >%}\~];?@&<#{|^[`/:=$+''"';
            l_reserved_chars TEXT DEFAULT ';/?:@&=+$[]';
            L_CHAR           TEXT;
            BEGIN
            IF (url IS NULL) THEN
                RETURN NULL;
            END IF;

            if not escape_reserved_chars then
                L_BAD := translate(L_BAD, l_reserved_chars, '');
            end if;

            FOR I IN 1..LENGTH(url) LOOP
                L_CHAR := SUBSTR(url, I, 1);
                IF (INSTR(L_BAD, L_CHAR) > 0 or ascii(L_CHAR) > 255) THEN
                L_TMP := L_TMP || regexp_replace(upper(REPLACE(convert_TO(L_CHAR, url_charset)::TEXT,'\x','')),'(.{2})',
                                                '%\1','g');
                ELSE
                L_TMP := L_TMP || L_CHAR;
                END IF;
            END LOOP;
            RETURN L_TMP;
            END; $$;
            $q$;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in utl_url.escape(text,bool,text): %', SQLERRM;
            rollback;
    end;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('utl_url.escape(''http://新年好.com'')', '''http://%E6%96%B0%E5%B9%B4%E5%A5%BD.com''') into l_result;
    select compat_tools.f_unit_test('utl_url.escape(''http://新年好.com'',1)', '''http%3A%2F%2F%E6%96%B0%E5%B9%B4%E5%A5%BD.com''') into l_result;
    -- =========================================================================

    -- =========================================================================
    -- utl_url.escape(text,bool,text)
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('FUNCTION', 'utl_url', 'unescape(text,text)', '1.0','sql')
        then
            execute $q$
            CREATE OR REPLACE FUNCTION UTL_URL.unescape(url IN TEXT, url_charset IN TEXT DEFAULT 'UTF8')
            RETURNS TEXT
            LANGUAGE sql
            IMMUTABLE NOT FENCED NOT SHIPPABLE
            AS $$
            select CONVERT_FROM(string_agg(CASE
                                            WHEN LENGTH(A) = 3 THEN
                                                REPLACE(A, '%','\x')::bytea
                                            ELSE
                                                A :: bytea
                                            END, '' :: bytea), url_charset)
                from (select a
                        from (select (regexp_matches(url, '(%..|.)', 'g')) [ 1 ] a ) ) A;
            $$;
            $q$;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in utl_url.unescape(text,text): %', SQLERRM;
            rollback;
    end;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('utl_url.unescape(''http://%E6%96%B0%E5%B9%B4%E5%A5%BD.com'')', '''http://新年好.com''') into l_result;
    select compat_tools.f_unit_test('utl_url.unescape(''http%3A%2F%2F%E6%96%B0%E5%B9%B4%E5%A5%BD.com'',''utf8'')', '''http://新年好.com''') into l_result;
    -- =========================================================================

    -- =========================================================================
    -- utl_encode
    -- 参考 :https://gitee.com/darkathena/opengauss-oracle/blob/main/oracle-package/utl_encode.sql
    -- =========================================================================
    -- utl_encode.base64_encode(raw)
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('FUNCTION', 'utl_encode', 'base64_encode(raw)', '1.0','sql')
        then
            execute $q$
            CREATE OR REPLACE FUNCTION UTL_ENCODE.base64_encode(r IN raw)
            RETURNS raw
            LANGUAGE SQL
            IMMUTABLE NOT FENCED NOT SHIPPABLE
            AS $$
            SELECT rawtohex(encode(rawsend(r),'base64'))::RAW;
            $$;
            $q$;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in utl_encode.base64_encode(raw): %', SQLERRM;
            rollback;
    end;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('UTL_ENCODE.base64_encode(rawtohex(''测试'')::raw)', '''3572574C364B2B56''::raw') into l_result;
    -- =========================================================================

    -- =========================================================================
    -- utl_encode.base64_decode(raw)
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('FUNCTION', 'utl_encode', 'base64_decode(raw)', '1.0','sql')
        then
            execute $q$
            CREATE OR REPLACE FUNCTION UTL_ENCODE.base64_decode(r IN raw)
            RETURNS raw
            LANGUAGE SQL
            IMMUTABLE NOT FENCED NOT SHIPPABLE
            AS $$
            SELECT rawout(decode(encode(rawsend(r),'escape'),'base64')::BYTEA)::TEXT::RAW;
            $$;
            $q$;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in utl_encode.base64_decode(raw): %', SQLERRM;
            rollback;
    end;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('UTL_ENCODE.base64_decode(''3572574C364B2B56'')', '''E6B58BE8AF95''::raw') into l_result;
    -- =========================================================================

    -- =========================================================================
    -- utl_encode.text_encode(text,text,int4)
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('FUNCTION', 'utl_encode', 'text_encode(text,text,int4)', '1.0')
        then
            execute $q$
            CREATE OR REPLACE FUNCTION utl_encode.text_encode(buf  in text,
                       encode_charset in text default 'UTF8'::text,
                       encoding       in int4 default 2::int4)
            RETURNS text
            LANGUAGE plpgsql
            IMMUTABLE NOT FENCED NOT SHIPPABLE
            AS $$
            DECLARE
            l_result text DEFAULT ''::text;
            begin
            if encoding=1 THEN
            l_result:= encode(CONVERT_TO(buf,encode_charset), 'base64') ;
            elsif encoding=2 THEN
            select string_agg( (case when ascii(s)<=255 AND s!='=' then s else
                    regexp_replace(upper(REPLACE(convert_TO(s, encode_charset)::TEXT,'\x','')),'(.{2})',
                                                            '=\1','g') end ),'') into l_result
                    from (select unnest(string_to_array(buf, null) ) s);
            else
            raise 'invaild encoding!';
            end if;
            return l_result;
            end;
            $$;
            $q$;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in utl_encode.text_encode(text,text,int4): %', SQLERRM;
            rollback;
    end;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('utl_encode.text_encode(''往1234\as df=AB'',encode_charset => ''GBK'')', '''=CD=F91234\as df=3DAB''') into l_result;
    select compat_tools.f_unit_test('utl_encode.text_encode(''往1234\as df=AB'',encode_charset => ''GBK'',encoding => 1)', '''zfkxMjM0XGFzIGRmPUFC''') into l_result;
    -- =========================================================================

    -- =========================================================================
    -- utl_encode.text_decode(text,text,int4)
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('FUNCTION', 'utl_encode', 'text_decode(text,text,int4)', '1.0')
        then
            execute $q$
            CREATE OR REPLACE FUNCTION utl_encode.text_decode(buf  in text,
                       encode_charset in text default 'UTF8'::text,
                       encoding       in int4 default 2::int4)
            RETURNS text
            LANGUAGE plpgsql
            IMMUTABLE NOT FENCED NOT SHIPPABLE
            AS $$
            DECLARE
            l_result text DEFAULT ''::text;
            begin
            if encoding=1 THEN
            l_result:= CONVERT_FROM(decode(buf, 'base64'),encode_charset) ;
            elsif encoding=2 THEN
            select CONVERT_FROM(string_agg(CASE
                                            WHEN LENGTH(A) = 3 THEN
                                            REPLACE(A, '=', '\x') :: bytea
                                            when a = '\' then
                                            '\\' :: bytea
                                            ELSE
                                            A :: bytea
                                        END, '' :: bytea), encode_charset) into l_result
            from (select a
                    from (select (regexp_matches(buf, '(=..|.)', 'g')) [ 1 ] a ) ) A;
            else
            raise 'invaild encoding!';
            end if;
            return l_result;
            end;
            $$;
            $q$;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in utl_encode.text_decode(text,text,int4): %', SQLERRM;
            rollback;
    end;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('utl_encode.text_decode(''=CD=F91234\as df=3DAB'',encode_charset => ''GBK'',encoding => 2)', '''往1234\as df=AB''') into l_result;
    select compat_tools.f_unit_test('utl_encode.text_decode(''zfkxMjM0XGFzIGRmPUFC'',encode_charset => ''GBK'',encoding => 1)', '''往1234\as df=AB''') into l_result;
    -- =========================================================================

    -- =========================================================================
    -- utl_encode.quoted_printable_encode(raw)
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('FUNCTION', 'utl_encode', 'quoted_printable_encode(raw)', '1.0')
        then
            execute $q$
            CREATE OR REPLACE FUNCTION utl_encode.quoted_printable_encode(r in raw)
            RETURNS raw
            LANGUAGE plpgsql
            IMMUTABLE NOT FENCED NOT SHIPPABLE
            AS $$
            DECLARE
            l_result raw DEFAULT ''::raw;
            l_def_charset text DEFAULT 'utf8';
            begin
            select pg_encoding_to_char(encoding) into l_def_charset from pg_database where datname=current_database();
            select rawtohex(string_agg((case
                                        when ascii(s) <= 255  AND s not in('=', '\') then
                                            s
                                        when s = '\' then
                                            '\\'
                                        else
                                            regexp_replace(upper(REPLACE(convert_TO(s, l_def_charset) :: TEXT, '\x', '')), '(.{2})', '=\1', 'g')
                                        end), '')) :: raw into l_result
            from (select unnest(string_to_array(convert_from(rawsend(r), l_def_charset), null) ) s );
            return l_result;
            end;
            $$;
            $q$;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in utl_encode.quoted_printable_encode(raw): %', SQLERRM;
            rollback;
    end;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('utl_encode.quoted_printable_encode( ''E5BE80313233345C61732064663D4142'')', '''3D45353D42453D3830313233345C61732064663D33444142''') into l_result;
    -- =========================================================================

    -- =========================================================================
    -- utl_encode.quoted_printable_decode(raw)
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('FUNCTION', 'utl_encode', 'quoted_printable_decode(raw)', '1.0')
        then
            execute $q$
            CREATE OR REPLACE FUNCTION utl_encode.quoted_printable_decode(r in raw)
            RETURNS raw
            LANGUAGE plpgsql
            IMMUTABLE NOT FENCED NOT SHIPPABLE
            AS $$
            DECLARE
            l_result raw DEFAULT ''::raw;
            l_def_charset text DEFAULT 'utf8';
            begin
            select pg_encoding_to_char(encoding) into l_def_charset from pg_database where datname=current_database();
            select rawout(string_agg(CASE
                                            WHEN LENGTH(A) = 3 THEN
                                            REPLACE(A, '=', '\x') :: bytea
                                            when a = '\' then
                                            '\\' :: bytea
                                            ELSE
                                            A :: bytea
                                        END, '' :: bytea))::text::raw into l_result
            from (select a
                    from (select (regexp_matches(convert_from(rawsend(r), l_def_charset), '(=..|.)', 'g')) [ 1 ] a ) ) A;
            return l_result;
            end;
            $$;
            $q$;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in utl_encode.quoted_printable_decode(raw): %', SQLERRM;
            rollback;
    end;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('utl_encode.quoted_printable_decode( ''3D45353D42453D3830313233345C61732064663D33444142'')', '''E5BE80313233345C61732064663D4142''') into l_result;
    -- =========================================================================

    -- =========================================================================
    -- utl_encode.mimeheader_encode(text,text,int4)
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('FUNCTION', 'utl_encode', 'mimeheader_encode(text,text,int4)', '1.0')
        then
            execute $q$
            CREATE OR REPLACE FUNCTION utl_encode.mimeheader_encode(buf  in text,
                       encode_charset in text default 'UTF8'::text,
                       encoding       in int4 default 2::int4)
            RETURNS text
            LANGUAGE plpgsql
            IMMUTABLE NOT FENCED NOT SHIPPABLE
            AS $$
            DECLARE
            l_result text DEFAULT ''::text;
            begin
            if encoding=1 THEN
            l_result:= encode(CONVERT_TO(buf,encode_charset), 'base64') ;
            elsif encoding=2 THEN
            select string_agg( (case when ascii(s)<=255 AND s NOT IN ('=',' ','?') then s else
                    regexp_replace(upper(REPLACE(convert_TO(s, encode_charset)::TEXT,'\x','')),'(.{2})',
                                                            '=\1','g') end ),'') into l_result
                    from (select unnest(string_to_array(buf, null) ) s);
            else
            raise 'invaild encoding!';
            end if;
            return '=?'||encode_charset||'?'||CASE WHEN encoding=2 THEN 'Q' ELSE 'B' END||'?'||l_result||'?=';
            end;
            $$;
            $q$;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in utl_encode.mimeheader_encode(text,text,int4): %', SQLERRM;
            rollback;
    end;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('UTL_ENCODE.MIMEHEADER_ENCODE(''What is the date 王 =20 / \?'',encode_charset =>''GBK'' )', '''=?GBK?Q?What=20is=20the=20date=20=CD=F5=20=3D20=20/=20\=3F?=''') into l_result;
    select compat_tools.f_unit_test('UTL_ENCODE.MIMEHEADER_ENCODE(''What is the date 王 =20 / \?'',encoding => 2 )', '''=?UTF8?Q?What=20is=20the=20date=20=E7=8E=8B=20=3D20=20/=20\=3F?=''') into l_result;
    select compat_tools.f_unit_test('UTL_ENCODE.MIMEHEADER_ENCODE(''What is the date 王 =20 / \?'',encode_charset =>''UTF8'' ,encoding => 1)', '''=?UTF8?B?V2hhdCBpcyB0aGUgZGF0ZSDnjosgPTIwIC8gXD8=?=''') into l_result;
    -- =========================================================================

    -- =========================================================================
    -- utl_encode.mimeheader_decode(text)
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('FUNCTION', 'utl_encode', 'mimeheader_decode(text)', '1.0')
        then
            execute $q$
            CREATE OR REPLACE FUNCTION utl_encode.mimeheader_decode(buf in text)
            RETURNS text
            LANGUAGE plpgsql
            IMMUTABLE NOT FENCED NOT SHIPPABLE
            AS $$
            DECLARE
            l_encode_charset text;
            l_encoding text;
            l_result text DEFAULT ''::text;
            l_buf text;
            begin
            l_encode_charset:=REGEXP_SUBSTR(buf,'(?<=\=\?)(.+?)(?=\?.\?)');
            l_encoding:=REGEXP_SUBSTR(buf,'(?<=\?)(.?)(?=\?)');
            l_buf:=REGEXP_SUBSTR(buf,'(?<=\?.\?)(.+?)(?=\?\=)');
            if l_encoding='B' THEN
            l_result:= CONVERT_FROM(decode(l_buf, 'base64'),l_encode_charset) ;
            elsif l_encoding='Q' THEN
            select CONVERT_FROM(string_agg(CASE
                                            WHEN LENGTH(A) = 3 THEN
                                            REPLACE(A, '=', '\x') :: bytea
                                            when a = '\' then
                                            '\\' :: bytea
                                            ELSE
                                            A :: bytea
                                        END, '' :: bytea), l_encode_charset) into l_result
            from (select a
                    from (select (regexp_matches(l_buf, '(=..|.)', 'g')) [ 1 ] a ) ) A;
            else
            raise 'invaild encoding!';
            end if;
            return l_result;
            end;
            $$;
            $q$;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in utl_encode.mimeheader_decode(text): %', SQLERRM;
            rollback;
    end;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('UTL_ENCODE.mimeheader_decode(''=?UTF-8?Q?What=20is=20the=20date=20=E7=8E=8B=20=3D20=20/=20\=3F?='')', '''What is the date 王 =20 / \?''') into l_result;
    select compat_tools.f_unit_test('UTL_ENCODE.mimeheader_decode(''=?UTF8?B?V2hhdCBpcyB0aGUgZGF0ZSDnjosgPTIwIC8gXD8=?='')', '''What is the date 王 =20 / \?''') into l_result;
    -- =========================================================================

    -- =========================================================================
    -- utl_encode.uuencode(raw,int1,text,text)
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('FUNCTION', 'utl_encode', 'uuencode(raw,int1,text,text)', '2.0')
        then
            execute $q$
            create or replace function UTL_ENCODE.uuencode(r   in raw,
                    type       in int1 default 1::int1,
                    filename   in text default 'uuencode.txt',
                    permission in text default '0')
            RETURNS raw
            LANGUAGE plpgsql
            IMMUTABLE NOT FENCED NOT SHIPPABLE
            AS $$
            DECLARE
            /*Oracle自带函数有BUG(doc id 2197134.1),未遵循uuencode标准,官方不建议使用
                而本函数按uuencode标准生成 -- DarkAthena 2022-02-14*/
            l_result text DEFAULT ''::text;
            l_pos int4 DEFAULT 1;
            l_3_bytes text DEFAULT ''::text;
            l_new_4_chr text DEFAULT ''::text;
            l_full_str text DEFAULT ''::text;
            l_line_num int4 DEFAULT 1;
            l_line_str text DEFAULT ''::text;
            l_line_len int4 DEFAULT 60::int4;
            begin
            if type not in (1,2,3,4) THEN
            RAISE  'input type error!';
            end if;
            loop
            l_3_bytes:=substring(r from l_pos*6-5 for 6);
            if length(l_3_bytes)=0 or l_3_bytes is null THEN
            exit;
            elsif length(l_3_bytes)!=6 THEN
            l_3_bytes:=rpad(l_3_bytes,6,'0');
            end if;
            select
            chr(SUBSTRING(a from 1 for 6)::int +32)||
            chr(SUBSTRING(a from 7 for 6)::int+32)||
            chr(SUBSTRING(a from 13 for 6)::int+32)||
            chr(SUBSTRING(a from 19 for 6)::int+32) into l_new_4_chr
            from
            (select to_number(l_3_bytes,'xxxxxx')::int::bit(24) a);
            l_full_str:=l_full_str||l_new_4_chr;
            l_pos:=l_pos+1;
            end loop;
            loop
            l_line_str:=substring(l_full_str from l_line_num*(l_line_len)-(l_line_len-1) for l_line_len);
            if length(l_line_str)=0 or l_line_str is null then
            exit;
            end if;
            l_result:=l_result||chr((length(l_line_str))/4*3+32)||l_line_str||chr(13)||chr(10);
            l_line_num:=l_line_num+1;
            end loop;
            l_result:=replace(l_result,' ','`');
            l_result:=substr(l_result,1,length(l_result)-2);
            if type in (1,2) then
            l_result:='begin '||permission||' '||filename||chr(13)||chr(10)||l_result;
            end if;
            if type in (1,4) then
            l_result:=l_result||chr(13)||chr(10)||'`'||chr(13)||chr(10)||'end';
            end if;

            return rawtohex(replace(l_result,'\','\\'))::raw;
            end;
            $$;
            $q$;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in utl_encode.uuencode(raw,int1,text,text): %', SQLERRM;
            rollback;
    end;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('UTL_ENCODE.uuencode(''6162636465666731323334''::raw)', '''626567696E2030207575656E636F64652E7478740D0A2C3836294339263546395324522C5330600D0A600D0A656E64''::raw') into l_result;
    select compat_tools.f_unit_test('UTL_ENCODE.uuencode(''6162636465666731323334'',3)', '''2C3836294339263546395324522C533060''') into l_result;
    -- =========================================================================

    -- =========================================================================
    -- utl_encode.uudecode(raw)
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('FUNCTION', 'utl_encode', 'uudecode(raw)', '2.0')
        then
            execute $q$
            create or replace function UTL_ENCODE.uudecode(r in raw)
            RETURNS raw
            LANGUAGE plpgsql
            IMMUTABLE NOT FENCED NOT SHIPPABLE
            AS $$
            DECLARE
            /*Oracle自带函数有BUG(doc id 2197134.1),未遵循uuencode标准,官方不建议使用
                而本函数按uuencode标准解析-- DarkAthena 2022-02-14*/
            l_ori_str text DEFAULT ''::text;
            l_result text DEFAULT ''::text;
            l_pos int4 DEFAULT 1;
            l_3_bytes text DEFAULT ''::text;
            l_new_4_chr text DEFAULT ''::text;
            l_full_str text DEFAULT ''::text;
            l_line_num int4 DEFAULT 1;
            l_line_str text DEFAULT ''::text;
            l_line_len int4 DEFAULT 60::int4;
            begin
            SELECT convert_from(rawsend(r),
            (select pg_encoding_to_char(encoding) as encoding from pg_database where datname=current_database()))
            into l_ori_str;

            l_ori_str:=replace(l_ori_str,chr(13)||chr(10)||'`'||chr(13)||chr(10)||'end','');

            if substr(l_ori_str,1,5) ='begin' THEN
            l_ori_str:=substr(l_ori_str,instr(l_ori_str,chr(10))+1);
            end if ;

            LOOP
            l_line_str:=substr(l_ori_str,2+(l_line_len+3)*(l_line_num-1),l_line_len);
            if length(l_line_str)=0 or l_line_str is null then exit;
            end if;
            l_full_str:=l_full_str||l_line_str;
            l_line_num:=l_line_num+1;
            end loop;

            l_full_str:=replace(l_full_str,'`',' ');

            LOOP
            l_new_4_chr:=substring(l_full_str from 1+4*(l_pos-1) for 4);
            if length(l_new_4_chr)=0 or l_new_4_chr is null then exit;
            end if;
            select  to_char((listagg(substring( (ascii(a)-32)::bit(8) from 3 for 6)::text) within group(order by 1))::bit(24)::int,'fmxxxxxx')::raw
            into l_3_bytes
            from (select unnest(string_to_array(l_new_4_chr,null)) a);
            l_result:=l_result||l_3_bytes;
            l_pos:=l_pos+1;
            end LOOP;

            l_result:=regexp_replace(l_result,'(00)+$','');
            return l_result::raw;
            end;
            $$;
            $q$;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in utl_encode.uudecode(raw): %', SQLERRM;
            rollback;
    end;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('utl_encode.uudecode(rawtohex('',86)C9&5F9S$R,S0`'')::raw)', '''6162636465666731323334''') into l_result;
    select compat_tools.f_unit_test('utl_encode.uudecode(''626567696E2030207575656E636F64652E7478740D0A2C3836294339263546395324522C5330600D0A600D0A656E64''::raw)', '''6162636465666731323334''') into l_result;
    -- =========================================================================

    -- =========================================================================
    -- utl_raw
    -- 参考 :https://gitee.com/darkathena/opengauss-oracle/blob/main/oracle-package/utl_raw.sql
    -- =========================================================================
    -- utl_raw.cast_to_varchar2(raw)
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('FUNCTION', 'utl_raw', 'cast_to_varchar2(raw)', '1.0','sql')
        then
            execute $q$
            CREATE OR REPLACE FUNCTION UTL_RAW.cast_to_varchar2(r IN raw)
            RETURNS text
            LANGUAGE SQL
            IMMUTABLE NOT FENCED NOT SHIPPABLE
            AS $$
            SELECT convert_from(rawsend(r),(select pg_encoding_to_char(encoding) as encoding from pg_database where datname=current_database()));
            $$;
            $q$;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in utl_raw.cast_to_varchar2(raw): %', SQLERRM;
            rollback;
    end;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('utl_raw.cast_to_varchar2(''43616D65726F6E'')', '''Cameron''') into l_result;
    -- =========================================================================

    -- =========================================================================
    -- utl_raw.cast_to_raw(text)
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('FUNCTION', 'utl_raw', 'cast_to_raw(text)', '1.0','sql')
        then
            execute $q$
            CREATE OR REPLACE FUNCTION UTL_RAW.cast_to_raw(c IN text)
            RETURNS raw
            LANGUAGE SQL
            IMMUTABLE NOT FENCED NOT SHIPPABLE
            AS $$
            SELECT rawtohex(c)::raw;
            $$;
            $q$;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in utl_raw.cast_to_raw(text): %', SQLERRM;
            rollback;
    end;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('utl_raw.cast_to_raw(''Cameron'')', '''43616D65726F6E''::raw') into l_result;
    -- =========================================================================

    -- =========================================================================
    -- utl_raw.concat(raw,raw,raw,raw,raw,raw,raw,raw,raw,raw,raw,raw)
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('FUNCTION', 'utl_raw', 'concat(raw,raw,raw,raw,raw,raw,raw,raw,raw,raw,raw,raw)', '1.0','sql')
        then
            execute $q$
            CREATE OR REPLACE FUNCTION UTL_RAW.concat(
            r1  IN raw DEFAULT ''::raw,
            r2  IN raw DEFAULT ''::raw,
            r3  IN raw DEFAULT ''::raw,
            r4  IN raw DEFAULT ''::raw,
            r5  IN raw DEFAULT ''::raw,
            r6  IN raw DEFAULT ''::raw,
            r7  IN raw DEFAULT ''::raw,
            r8  IN raw DEFAULT ''::raw,
            r9  IN raw DEFAULT ''::raw,
            r10 IN raw DEFAULT ''::raw,
            r11 IN raw DEFAULT ''::raw,
            r12 IN raw DEFAULT ''::raw
            )
            RETURNS raw
            LANGUAGE SQL
            IMMUTABLE NOT FENCED NOT SHIPPABLE
            AS $$
            SELECT (r1||r2||r3||r4||r5||r6||r7||r8||r9||r10||r11||r12)::raw;
            $$;
            $q$;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in utl_raw.concat(raw,raw,raw,raw,raw,raw,raw,raw,raw,raw,raw,raw): %', SQLERRM;
            rollback;
    end;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('utl_raw.concat( ''9'', ''0102'', ''ff'', ''0a2b'' )', '''090102FF0A2B''::raw') into l_result;
    -- =========================================================================

    -- =========================================================================
    -- utl_raw.length(raw)
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('FUNCTION', 'utl_raw', 'length(raw)', '1.0','sql')
        then
            execute $q$
            CREATE OR REPLACE FUNCTION UTL_RAW.length(r IN raw)
            RETURNS int4
            LANGUAGE SQL
            IMMUTABLE NOT FENCED NOT SHIPPABLE
            AS $$
            SELECT (pg_catalog.length(r)/2)::int4;
            $$;
            $q$;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in utl_raw.length(raw): %', SQLERRM;
            rollback;
    end;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('UTL_RAW.length(''FF''::raw)', '1') into l_result;
    -- =========================================================================

    -- =========================================================================
    -- utl_raw.substr(raw,int4,int4)
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('FUNCTION', 'utl_raw', 'substr(raw,int4,int4)', '1.0')
        then
            execute $q$
            CREATE OR REPLACE FUNCTION UTL_RAW.substr(r IN raw,pos in int4,len in int4 DEFAULT null)
            RETURNS raw
            LANGUAGE plpgSQL
            IMMUTABLE NOT FENCED NOT SHIPPABLE
            AS $$
            DECLARE
            BEGIN
            if pos>0 then
            return (case when len is not null
                        then  substring(r from pos*2-1 for len*2)
                        else substring(r from pos*2-1) end )::raw ;
            ELSE

            return (case when len is not null
                        then  pg_catalog.substr(r , pos*2 , len*2)
                        else pg_catalog.substr(r , pos*2) end )::raw ;
            end if;
            end;
            $$;
            $q$;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in utl_raw.substr(raw,int4,int4): %', SQLERRM;
            rollback;
    end;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('utl_raw.substr( ''0102030405'', 3, 2 )', '''0304''::raw') into l_result;
    select compat_tools.f_unit_test('utl_raw.substr( ''0102030405'', -2, 1 )', '''04''::raw') into l_result;
    -- =========================================================================

    -- =========================================================================
    -- utl_raw.transliterate(raw,raw,raw,raw)
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('FUNCTION', 'utl_raw', 'transliterate(raw,raw,raw,raw)', '1.0')
        then
            execute $q$
            CREATE OR REPLACE FUNCTION UTL_RAW.transliterate(
            r IN raw,
            to_set in raw DEFAULT ''::raw,
            from_set in raw DEFAULT ''::raw,
            pad IN raw DEFAULT '00'::raw )
            RETURNS raw
            LANGUAGE plpgsql
            IMMUTABLE NOT FENCED NOT SHIPPABLE
            AS $$
            DECLARE
            l_tmp text;
            begin
            if pg_catalog.length(pad)/2>1 then
            RAISE  'Error in utl_raw.transliterate: pad must be 1 byte or null!';
            end if;
            l_tmp:=regexp_replace(r::TEXT, '(..)','=\1', 'g');
            for rec in (
            select fs_str,nvl(ts_str,pad::TEXT) ts_str from
            (select row_number() over() fs_pos,fs_str from (
            select (regexp_matches(from_set::TEXT, '(..)', 'g')) [ 1 ] fs_str)) f
            left join
            (select row_number() over() ts_pos,ts_str from (
            select (regexp_matches(to_set::TEXT, '(..)', 'g')) [ 1 ] ts_str)) t
            on f.fs_pos=t.ts_pos
            ) loop
            l_tmp:=replace(l_tmp,'='||rec.fs_str,rec.ts_str);
            end loop;
            return replace(l_tmp,'=','')::raw;
            end;
            $$;
            $q$;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in utl_raw.transliterate(raw,raw,raw,raw): %', SQLERRM;
            rollback;
    end;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('utl_raw.transliterate( ''010203040502'', ''0809'', ''01020304'', ''0a'' )', '''08090A0A0509''::raw') into l_result;
    -- =========================================================================

    -- =========================================================================
    -- utl_raw.translate(raw,raw,raw)
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('FUNCTION', 'utl_raw', 'translate(raw,raw,raw)', '1.0','sql')
        then
            execute $q$
            CREATE OR REPLACE FUNCTION UTL_RAW.translate(r IN raw,from_set in raw,to_set in raw )
            RETURNS raw
            LANGUAGE sql
            IMMUTABLE NOT FENCED NOT SHIPPABLE
            AS $$
            select UTL_RAW.transliterate(r,to_set,from_set,''::raw);
            $$;
            $q$;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in utl_raw.translate(raw,raw,raw): %', SQLERRM;
            rollback;
    end;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('utl_raw.translate( ''0102030405'', ''0203'', ''06'' )', '''01060405''::raw') into l_result;
    -- =========================================================================

    -- =========================================================================
    -- utl_raw.copies(raw,int8)
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('FUNCTION', 'utl_raw', 'copies(raw,int8)', '1.0')
        then
            execute $q$
            CREATE OR REPLACE FUNCTION UTL_RAW.copies(
            r IN raw,
            n IN int8)
            RETURNS raw
            LANGUAGE plpgsql
            IMMUTABLE NOT FENCED NOT SHIPPABLE
            AS $$
            DECLARE
            l_result raw DEFAULT ''::raw;
            begin
            if n<0 then
            RAISE  'n must be equal or greater than 1!';
            end if;
            if pg_catalog.length(r)/2<1 then
            RAISE  'r is missing, null and/or 0 length!';
            end if;
            for i in 1..n LOOP
            l_result:=l_result||r;
            end loop;
            return l_result;
            end;
            $$;
            $q$;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in utl_raw.copies(raw,int8): %', SQLERRM;
            rollback;
    end;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('utl_raw.copies( ''010203'', 4 )', '''010203010203010203010203''::raw') into l_result;
    -- =========================================================================

    -- =========================================================================
    -- utl_raw.overlay(raw,raw,int4,int4,raw)
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('FUNCTION', 'utl_raw', 'overlay(raw,raw,int4,int4,raw)', '1.0')
        then
            execute $q$
            CREATE OR REPLACE FUNCTION UTL_RAW.overlay(
            overlay_str IN raw,
            target      IN raw,
            pos         IN int4 DEFAULT 1,
            len         IN int4 DEFAULT NULL,
            pad         IN raw            DEFAULT '00'::raw)
            RETURNS raw
            LANGUAGE plpgsql
            IMMUTABLE NOT FENCED NOT SHIPPABLE
            AS $$
            DECLARE
            l_result raw;
            l_overlay_str raw;
            l_len int4;
            l_pad raw;
            l_pos_over_str raw DEFAULT ''::raw;
            begin
            l_overlay_str:=overlay_str;
            if UTL_RAW.length(pad)>1 THEN
            l_pad:=UTL_RAW.substr(pad , 1 , 1);
            ELSE
            l_pad:=pad;
            end if;
            if len is null then
            l_len:=UTL_RAW.length(overlay_str);
            else
            l_len:=len;
            end if;
            if UTL_RAW.length(l_overlay_str)>l_len THEN
            l_overlay_str:=UTL_RAW.substr(l_overlay_str , 1 , l_len);
            elsif UTL_RAW.length(l_overlay_str)<l_len THEN
            l_overlay_str:=l_overlay_str||UTL_RAW.copies(l_pad,l_len-UTL_RAW.length(l_overlay_str) );
            end if;
            if pos>utl_raw.length(target) THEN
            l_pos_over_str:=UTL_RAW.copies(pad,pos-utl_raw.length(target)-1);
            end if;
            l_result:=UTL_RAW.substr(target , 1 , pos-1)||l_pos_over_str||l_overlay_str||UTL_RAW.substr(target , pos+l_len);
            return l_result;
            end;
            $$;
            $q$;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in utl_raw.overlay(raw,raw,int4,int4,raw): %', SQLERRM;
            rollback;
    end;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('utl_raw.overlay( ''aabb'', ''010203'' )', '''AABB03''::raw') into l_result;
    select compat_tools.f_unit_test('utl_raw.overlay( ''aabb'', ''010203'',2 )', '''01AABB''::raw') into l_result;
    select compat_tools.f_unit_test('utl_raw.overlay( ''aabb'', ''010203'',5 )', '''01020300AABB''::raw') into l_result;
    select compat_tools.f_unit_test('utl_raw.overlay( ''aabb'', ''010203'',2,1 )', '''01AA03''::raw') into l_result;
    select compat_tools.f_unit_test('utl_raw.overlay( ''aabb'', ''010203'',5,1,''FF'' )', '''010203FFAA''::raw') into l_result;
    -- =========================================================================

    -- =========================================================================
    -- utl_raw.xrange(raw,raw)
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('FUNCTION', 'utl_raw', 'xrange(raw,raw)', '1.0')
        then
            execute $q$
            CREATE OR REPLACE FUNCTION UTL_RAW.xrange(
            start_byte IN raw,
            end_byte IN raw)
            RETURNS raw
            LANGUAGE plpgsql
            IMMUTABLE NOT FENCED NOT SHIPPABLE
            AS $$
            DECLARE
            l_result raw DEFAULT ''::raw;
            l_start_int int4;
            l_end_int int4;
            l_tmp raw;
            begin
            if UTL_RAW.length(start_byte)!=1 or UTL_RAW.length(end_byte)!=1  then
            RAISE  'start_byte and end_byte must be single byte!';
            end if;
            l_start_int:=to_number(start_byte,'xx');
            l_end_int:=to_number(end_byte,'xx');
            LOOP
            l_tmp:=utl_raw.substr(to_hex(l_start_int)::raw,-1,1);
            l_result:=l_result||l_tmp;
            if l_tmp=utl_raw.substr(end_byte,-1) then exit;
            end if;
            l_start_int:=l_start_int+1;
            end loop;
            return l_result;
            end;
            $$;
            $q$;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in utl_raw.xrange(raw,raw): %', SQLERRM;
            rollback;
    end;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('utl_raw.xrange( ''01'', ''11'' )', '''0102030405060708090A0B0C0D0E0F1011''::raw') into l_result;
    select compat_tools.f_unit_test('utl_raw.xrange( ''FA'', ''06'' )', '''FAFBFCFDFEFF00010203040506''::raw') into l_result;
    -- =========================================================================

    -- =========================================================================
    -- utl_raw.reverse(raw)
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('FUNCTION', 'utl_raw', 'reverse(raw)', '1.0')
        then
            execute $q$
            CREATE OR REPLACE FUNCTION UTL_RAW.reverse(
            r IN raw)
            RETURNS raw
            LANGUAGE plpgsql
            IMMUTABLE NOT FENCED NOT SHIPPABLE
            AS $$
            DECLARE
            l_result raw DEFAULT ''::raw;
            begin
            for i in reverse UTL_RAW.length(r)..1   LOOP
            l_result:=l_result||UTL_RAW.substr(r , i , 1);
            end loop;
            return l_result;
            end;
            $$;
            $q$;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in utl_raw.reverse(raw): %', SQLERRM;
            rollback;
    end;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('utl_raw.reverse( ''0102030405'' )', '''0504030201''::raw') into l_result;
    -- =========================================================================

    -- =========================================================================
    -- utl_raw.compare(raw,raw,raw)
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('FUNCTION', 'utl_raw', 'compare(raw,raw,raw)', '1.0')
        then
            execute $q$
            CREATE OR REPLACE FUNCTION UTL_RAW.compare(
                   r1  IN raw,
                   r2  IN raw,
                   pad IN raw DEFAULT '00'::raw)
            RETURNS int4
            LANGUAGE plpgsql
            IMMUTABLE NOT FENCED NOT SHIPPABLE
            AS $$
            DECLARE
            l_result int4 DEFAULT 0;
            l_r1 raw DEFAULT ''::raw;
            l_r2 raw DEFAULT ''::raw;
            begin
            for i in 1..greatest(UTL_RAW.length(r1),UTL_RAW.length(r2))   LOOP
            l_r1:=UTL_RAW.substr(r1 , i , 1);
            l_r2:=UTL_RAW.substr(r2 , i , 1);
            if l_r1!=l_r2 THEN
            if l_r1=''::raw then
            l_r1:=pad;
            end if;
            if l_r2=''::raw then
            l_r2:=pad;
            end if;
            if l_r1!=l_r2 then
            l_result:=i;
            EXIT;
            end if;
            end if;
            end loop;
            return l_result;
            end;
            $$;
            $q$;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in utl_raw.compare(raw,raw,raw): %', SQLERRM;
            rollback;
    end;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('utl_raw.compare( ''010203'', ''01020304'', ''04'' )', '0') into l_result;
    select compat_tools.f_unit_test('utl_raw.compare( ''01050304'', ''01020304'' )', '2') into l_result;
    -- =========================================================================

    -- =========================================================================
    -- utl_raw.convert(raw,text,text)
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('FUNCTION', 'utl_raw', 'convert(raw,text,text)', '1.0','sql')
        then
            execute $q$
            CREATE OR REPLACE FUNCTION UTL_RAW.convert(r IN raw,
                   to_charset   IN text,
                   from_charset IN text)
            RETURNS raw
            LANGUAGE sql
            IMMUTABLE NOT FENCED NOT SHIPPABLE
            AS $$
            select rawout(convert_to(convert_from(rawsend(r),from_charset),to_charset))::text::raw;
            $$;
            $q$;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in utl_raw.convert(raw,text,text): %', SQLERRM;
            rollback;
    end;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('utl_raw.convert(rawout(''测试''::BYTEA)::text::raw,''GBK'',''UTF8'')', '''B2E2CAD4''::raw') into l_result;
    -- =========================================================================

    -- =========================================================================
    -- utl_raw.bit_and(raw,raw)
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('FUNCTION', 'utl_raw', 'bit_and(raw,raw)', '1.0')
        then
            execute $q$
            CREATE OR REPLACE FUNCTION utl_raw.bit_and(r1 raw, r2 raw)
            RETURNS raw
            LANGUAGE plpgsql
            IMMUTABLE NOT FENCED NOT SHIPPABLE
            AS $$
            DECLARE
            l_result bytea DEFAULT ''::bytea;
            l_r1 raw;
            l_r2 raw;
            l_r1_bytea bytea;
            l_r2_bytea bytea;
            l_r1_len int8;
            l_r2_len int8;
            begin
            l_r1_len:=utl_raw.length(r1);
            l_r2_len:=utl_raw.length(r2);
            l_r1:=r1;
            l_r2:=r2;
            if l_r1_len>l_r2_len then
            l_r2:=utl_raw.copies('00'::raw,l_r1_len-l_r2_len)||l_r2;
            elsif l_r1_len<l_r2_len then
            l_r1:=utl_raw.copies('00'::raw,l_r2_len-l_r1_len)||l_r2;
            end if;
            l_r1_bytea:=rawsend(l_r1);
            l_r2_bytea:=rawsend(l_r2);
            l_result:=rawsend(utl_raw.copies('00'::raw,GREATEST(l_r1_len,l_r2_len)));
            for i in 0..bit_length(l_result)-1 LOOP
            l_result:=set_bit(l_result,i,get_bit(l_r1_bytea,i)&get_bit(l_r2_bytea,i));
            end loop;
            return rawout(l_result)::text::raw;
            end;
            $$;
            $q$;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in utl_raw.bit_and(raw,raw): %', SQLERRM;
            rollback;
    end;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('utl_raw.bit_and(''1234ffdd'',''1234ffee'')', '''1234FFCC''::raw') into l_result;
    -- =========================================================================

    -- =========================================================================
    -- utl_raw.bit_or(raw,raw)
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('FUNCTION', 'utl_raw', 'bit_or(raw,raw)', '1.0')
        then
            execute $q$
            CREATE OR REPLACE FUNCTION utl_raw.bit_or(r1 raw, r2 raw)
            RETURNS raw
            LANGUAGE plpgsql
            IMMUTABLE NOT FENCED NOT SHIPPABLE
            AS $$
            DECLARE
            l_result bytea DEFAULT ''::bytea;
            l_r1 raw;
            l_r2 raw;
            l_r1_bytea bytea;
            l_r2_bytea bytea;
            l_r1_len int8;
            l_r2_len int8;
            begin
            l_r1_len:=utl_raw.length(r1);
            l_r2_len:=utl_raw.length(r2);
            l_r1:=r1;
            l_r2:=r2;
            if l_r1_len>l_r2_len then
            l_r2:=utl_raw.copies('00'::raw,l_r1_len-l_r2_len)||l_r2;
            elsif l_r1_len<l_r2_len then
            l_r1:=utl_raw.copies('00'::raw,l_r2_len-l_r1_len)||l_r2;
            end if;
            l_r1_bytea:=rawsend(l_r1);
            l_r2_bytea:=rawsend(l_r2);
            l_result:=rawsend(utl_raw.copies('00'::raw,GREATEST(l_r1_len,l_r2_len)));
            for i in 0..bit_length(l_result)-1 LOOP
            l_result:=set_bit(l_result,i,get_bit(l_r1_bytea,i)|get_bit(l_r2_bytea,i));
            end loop;
            return rawout(l_result)::text::raw;
            end;
            $$;
            $q$;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in utl_raw.bit_or(raw,raw): %', SQLERRM;
            rollback;
    end;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('utl_raw.bit_or(''1234ffdd'',''1234ffee'')', '''1234FFFF''::raw') into l_result;
    -- =========================================================================

    -- =========================================================================
    -- utl_raw.bit_xor(raw,raw)
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('FUNCTION', 'utl_raw', 'bit_xor(raw,raw)', '1.0')
        then
            execute $q$
            CREATE OR REPLACE FUNCTION utl_raw.bit_xor(r1 raw, r2 raw)
            RETURNS raw
            LANGUAGE plpgsql
            IMMUTABLE NOT FENCED NOT SHIPPABLE
            AS $$
            DECLARE
            l_result bytea DEFAULT ''::bytea;
            l_r1 raw;
            l_r2 raw;
            l_r1_bytea bytea;
            l_r2_bytea bytea;
            l_r1_len int8;
            l_r2_len int8;
            begin
            l_r1_len:=utl_raw.length(r1);
            l_r2_len:=utl_raw.length(r2);
            l_r1:=r1;
            l_r2:=r2;
            if l_r1_len>l_r2_len then
            l_r2:=utl_raw.copies('00'::raw,l_r1_len-l_r2_len)||l_r2;
            elsif l_r1_len<l_r2_len then
            l_r1:=utl_raw.copies('00'::raw,l_r2_len-l_r1_len)||l_r2;
            end if;
            l_r1_bytea:=rawsend(l_r1);
            l_r2_bytea:=rawsend(l_r2);
            l_result:=rawsend(utl_raw.copies('00'::raw,GREATEST(l_r1_len,l_r2_len)));
            for i in 0..bit_length(l_result)-1 LOOP
            l_result:=set_bit(l_result,i,get_bit(l_r1_bytea,i)#get_bit(l_r2_bytea,i));
            end loop;
            return rawout(l_result)::text::raw;
            end;
            $$;
            $q$;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in utl_raw.bit_xor(raw,raw): %', SQLERRM;
            rollback;
    end;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('utl_raw.bit_xor(''1234ffdd'',''1234ffee'')', '''00000033''::raw') into l_result;
    -- =========================================================================

    -- =========================================================================
    -- utl_raw.bit_complement(raw)
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('FUNCTION', 'utl_raw', 'bit_complement(raw)', '1.0')
        then
            execute $q$
            CREATE OR REPLACE FUNCTION utl_raw.bit_complement(r raw)
            RETURNS raw
            LANGUAGE plpgsql
            IMMUTABLE NOT FENCED NOT SHIPPABLE
            AS $$
            DECLARE
            l_result bytea DEFAULT ''::bytea;
            begin
            l_result:=rawsend(r);
            for i in 0..bit_length(l_result)-1 LOOP
            l_result:=set_bit(l_result,i,case when get_bit(l_result,i)=1 then 0 else 1 end );
            end loop;
            return rawout(l_result)::text::raw;
            end;
            $$;
            $q$;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in utl_raw.bit_complement(raw): %', SQLERRM;
            rollback;
    end;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('UTL_raw.bit_complement(''1122FF'')', '''EEDD00''::raw') into l_result;
    -- =========================================================================

    -- =========================================================================
    -- utl_raw.cast_to_number(raw)
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('FUNCTION', 'utl_raw', 'cast_to_number(raw)', '1.0')
        then
            execute $q$
            CREATE OR REPLACE FUNCTION utl_raw.cast_to_number(r raw)
            RETURNS NUMERIC
            LANGUAGE plpgsql
            IMMUTABLE NOT FENCED NOT SHIPPABLE
            AS $$
            /*2022-02-08 memo:未对输入参数的正确性进行校验，错误的输入会带来错误的输出（by:DarkAthena）*/
            DECLARE
            l_result NUMERIC DEFAULT 0::numeric;
            l_first_byte int4;
            l_len int8;
            begin
            l_len:=utl_raw.length(r);
            l_first_byte:=to_number(utl_raw.substr(r,1,1),'xx');
            if l_first_byte>128 then
            for i in 1..l_len-1 LOOP
            l_result:=l_result+(to_number(utl_raw.substr(r,i+1,1),'xx')-1)*(100^(l_first_byte-193-(i-1)));
            end loop;
            elsif l_first_byte<128 then
            for i in 1..l_len-2 LOOP
            l_result:=l_result-(101-to_number(utl_raw.substr(r,i+1,1),'xx'))*(100^(62-l_first_byte-(i-1)));
            end loop;
            elsif l_first_byte=128 then
            l_result:=0;
            else
            RAISE  'raw format error!';
            end if;
            return l_result;
            end;
            $$;
            $q$;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in utl_raw.cast_to_number(raw): %', SQLERRM;
            rollback;
    end;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('utl_raw.cast_TO_number(''C1020B'')', '1.1') into l_result;
    select compat_tools.f_unit_test('utl_raw.cast_TO_number(''3E645B66'')', '-1.1') into l_result;
    -- =========================================================================

    -- =========================================================================
    -- utl_raw.cast_from_number(numeric)
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('FUNCTION', 'utl_raw', 'cast_from_number(numeric)', '1.0')
        then
            execute $q$
            CREATE OR REPLACE FUNCTION utl_raw.cast_from_number(n NUMERIC)
            RETURNS raw
            LANGUAGE plpgsql
            IMMUTABLE NOT FENCED NOT SHIPPABLE
            AS $$
            DECLARE
            l_result raw DEFAULT ''::raw;
            l_len int4;
            l_x NUMERIC;
            l_pos int4 DEFAULT 1::int4;
            begin
            l_len:=ceil((pg_catalog.length(ceil(abs(n)))/2))::int8;
            if n>0 then
            l_result:=to_hex(193+(l_len-1))::raw;
            LOOP
            l_x:=trunc(n,-(l_len-l_pos)*2)-trunc(n,-(l_len-l_pos+1)*2);
            l_result:=rawcat(l_result,to_hex((l_x/(100^(l_len-l_pos))+1)::int4)::raw);
            if trunc(n,-(l_len-l_pos)*2)=n then
            exit;
            end if;
            l_pos:=l_pos+1;
            end loop;
            elsif n<0 then
            l_result:=to_hex(62-(l_len-1))::raw;
            LOOP
            l_x:=trunc(n,-(l_len-l_pos)*2)-trunc(n,-(l_len-l_pos+1)*2);
            l_result:=rawcat(l_result,to_hex(101+(l_x/(100^(l_len-l_pos)))::int4)::raw);
            if trunc(n,-(l_len-l_pos)*2)=n then
            exit;
            end if;
            l_pos:=l_pos+1;
            end loop;
            l_result:=rawcat(l_result,'66'::raw);
            elsif n=0 then
            l_result:='80'::raw;
            else
            RAISE  'NUMERIC error!';
            end if;
            return l_result;
            end;
            $$;
            $q$;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in utl_raw.cast_from_number(numeric): %', SQLERRM;
            rollback;
    end;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('utl_raw.cast_from_number(1.1)', '''C1020B''::raw') into l_result;
    select compat_tools.f_unit_test('utl_raw.cast_from_number(-1.1)', '''3E645B66''::raw') into l_result;
    -- =========================================================================

    -- =========================================================================
    -- utl_raw.cast_to_binary_integer(raw,int1)
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('FUNCTION', 'utl_raw', 'cast_to_binary_integer(raw,int1)', '1.0')
        then
            execute $q$
            CREATE OR REPLACE FUNCTION utl_raw.cast_to_binary_integer(r IN RAW,
                                  endianess IN int1  DEFAULT 1)
            RETURNS int4
            LANGUAGE plpgsql
            IMMUTABLE NOT FENCED NOT SHIPPABLE
            AS $$
            DECLARE
            l_result int4;
            begin
            if endianess in (1,3) then
            l_result:=to_number(r::text,'xxxxxxxx');
            elsif endianess =2 then
            l_result:=to_number(utl_raw.reverse(r)::text,'xxxxxxxx');
            else
            RAISE  'invaild endianess!';
            end if;
            return l_result;
            end;
            $$;
            $q$;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in utl_raw.cast_to_binary_integer(raw,int1): %', SQLERRM;
            rollback;
    end;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('utl_raw.cast_to_binary_integer(''FF00'')', '65280') into l_result;
    select compat_tools.f_unit_test('utl_raw.cast_to_binary_integer(''FF00'',2)', '255') into l_result;
    -- =========================================================================

    -- =========================================================================
    -- utl_raw.cast_from_binary_integer(int4,int1)
    -- =========================================================================
    begin
        --移除int8版本
        select compat_tools.drop_compat_package('FUNCTION', 'utl_raw', 'cast_from_binary_integer(int8,int1)', '2.0') into l_result;
        delete from compat_tools.compat_version where object_name = 'utl_raw.cast_from_binary_integer(int8,int1)';
        delete from temp_result where object_name = 'utl_raw.cast_from_binary_integer(int8,int1)';

        if compat_tools.drop_compat_package('FUNCTION', 'utl_raw', 'cast_from_binary_integer(int4,int1)', '2.0')
        then
            execute $q$
            CREATE OR REPLACE FUNCTION utl_raw.cast_from_binary_integer(n IN int4,
                                  endianess IN int1  DEFAULT 1)
            RETURNS raw
            LANGUAGE plpgsql
            IMMUTABLE NOT FENCED NOT SHIPPABLE
            AS $$
            DECLARE
            l_result raw DEFAULT ''::raw;
            begin
            if endianess in (1,3) then
            l_result:=lpad(to_char(n,'fmxxxxxxxx'),8,'0')::raw;
            elsif endianess =2 then
            l_result:=utl_raw.reverse(replace(lpad(to_char(n,'fmxxxxxxxx'),8,'0'),' ','0')::raw);
            else
            RAISE  'invaild endianess!';
            end if;
            return l_result;
            end;
            $$;
            $q$;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in utl_raw.cast_from_binary_integer(int4,int1): %', SQLERRM;
            rollback;
    end;
    -- =========================================================================
    -- 测试用例：
    -- ----------
    select compat_tools.f_unit_test('utl_raw.cast_from_binary_integer(65280)', '''0000FF00''::RAW') into l_result;
    select compat_tools.f_unit_test('utl_raw.cast_from_binary_integer(65280,2)', '''00FF0000''::RAW') into l_result;
    select compat_tools.f_unit_test('utl_raw.cast_from_binary_integer(-652800)', '''FFF60A00''::RAW') into l_result;
    -- =========================================================================

    -- =========================================================================
    -- dbms_lob
    -- 参考:https://gitee.com/darkathena/opengauss-oracle/blob/main/oracle-package/dbms_lob.sql
    -- =========================================================================
    -- dbms_lob.file_readonly
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('FUNCTION', 'dbms_lob', 'file_readonly()', '1.0','sql')
        then
            execute $q$
            CREATE OR REPLACE FUNCTION dbms_lob.file_readonly()
            returns int4
            LANGUAGE sql
            IMMUTABLE NOT FENCED NOT SHIPPABLE
            AS $$
            SELECT 0::int4;
            $$;
            $q$;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_lob.file_readonly: %', SQLERRM;
            rollback;
    end;
    -- =========================================================================

    -- =========================================================================
    -- dbms_lob.lob_readonly
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('FUNCTION', 'dbms_lob', 'lob_readonly()', '1.0','sql')
        then
            execute $q$
            CREATE OR REPLACE FUNCTION DBMS_LOB.lob_readonly()
            returns int4
            LANGUAGE sql
            IMMUTABLE NOT FENCED NOT SHIPPABLE
            AS $$
            SELECT 0::int4;
            $$;
            $q$;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_lob.lob_readonly: %', SQLERRM;
            rollback;
    end;
    -- =========================================================================

    -- =========================================================================
    -- dbms_lob.lob_readwrite
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('FUNCTION', 'dbms_lob', 'lob_readwrite()', '1.0','sql')
        then
            execute $q$
            CREATE OR REPLACE FUNCTION DBMS_LOB.lob_readwrite()
            returns int4
            LANGUAGE sql
            IMMUTABLE NOT FENCED NOT SHIPPABLE
            AS $$
            SELECT 1::int4;
            $$;
            $q$;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_lob.lob_readwrite: %', SQLERRM;
            rollback;
    end;
    -- =========================================================================

    -- =========================================================================
    -- dbms_lob.lobmaxsize
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('FUNCTION', 'dbms_lob', 'lobmaxsize()', '1.0','sql')
        then
            execute $q$
            CREATE OR REPLACE FUNCTION DBMS_LOB.lobmaxsize()
            returns int4
            LANGUAGE sql
            IMMUTABLE NOT FENCED NOT SHIPPABLE
            AS $$
            --SELECT 18446744073709551615::int8; --只能int4
            SELECT 2147483647::int4;
            $$;
            $q$;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_lob.lobmaxsize: %', SQLERRM;
            rollback;
    end;
    -- =========================================================================

    -- =========================================================================
    -- dbms_lob.getlength(blob)
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('FUNCTION', 'dbms_lob', 'getlength(blob)', '1.0','sql')
        then
            execute $q$
            CREATE OR REPLACE FUNCTION DBMS_LOB.getlength(lob_loc in BLOB)
            RETURNS INT4
            LANGUAGE SQL
            IMMUTABLE NOT FENCED NOT SHIPPABLE
            AS $$
            SELECT pg_catalog.length(rawsend(lob_loc))::INT4;
            $$;
            $q$;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_lob.getlength(blob): %', SQLERRM;
            rollback;
    end;
    -- =========================================================================

    -- =========================================================================
    -- dbms_lob.getlength(clob)
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('FUNCTION', 'dbms_lob', 'getlength(clob)', '1.0','sql')
        then
            execute $q$
            CREATE OR REPLACE FUNCTION DBMS_LOB.getlength(lob_loc in clob)
            RETURNS INT4
            LANGUAGE SQL
            IMMUTABLE NOT FENCED NOT SHIPPABLE
            AS $$
            SELECT pg_catalog.length(lob_loc)::INT4;
            $$;
            $q$;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_lob.getlength(clob): %', SQLERRM;
            rollback;
    end;
    -- =========================================================================

    -- =========================================================================
    -- dbms_lob.open(blob,int)
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('PROCEDURE', 'dbms_lob', 'open(blob,int4)', '1.0')
        then
            execute $q$
            CREATE OR REPLACE PROCEDURE DBMS_LOB.open(lob_loc   IN OUT  BLOB,
                                            open_mode IN int4) package
            AS
            BEGIN
            null;
            end;
            $q$;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_lob.open(blob,int4): %', SQLERRM;
            rollback;
    end;
    -- =========================================================================

    -- =========================================================================
    -- dbms_lob.open(clob,int)
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('PROCEDURE', 'dbms_lob', 'open(clob,int4)', '1.0')
        then
            execute $q$
            CREATE OR REPLACE PROCEDURE DBMS_LOB.open(lob_loc   IN OUT  CLOB,
                                            open_mode IN int4) package
            AS
            BEGIN
            null;
            end;
            $q$;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_lob.open(clob,int4): %', SQLERRM;
            rollback;
    end;
    -- =========================================================================

    -- =========================================================================
    -- dbms_lob.freetemporary(blob)
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('PROCEDURE', 'dbms_lob', 'freetemporary(blob)', '1.0')
        then
            execute $q$
            CREATE OR REPLACE PROCEDURE DBMS_LOB.freetemporary(lob_loc IN OUT BLOB)
            package
            AS
            BEGIN
            null;
            end;
            $q$;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_lob.freetemporary(blob): %', SQLERRM;
            rollback;
    end;
    -- =========================================================================

    -- =========================================================================
    -- dbms_lob.freetemporary(clob)
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('PROCEDURE', 'dbms_lob', 'freetemporary(clob)', '1.0')
        then
            execute $q$
            CREATE OR REPLACE PROCEDURE DBMS_LOB.freetemporary(lob_loc IN OUT CLOB)
            package
            AS
            BEGIN
            null;
            end;
            $q$;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_lob.freetemporary(clob): %', SQLERRM;
            rollback;
    end;
    -- =========================================================================

    -- =========================================================================
    -- dbms_lob.substr(blob,int4,int4)
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('FUNCTION', 'dbms_lob', 'substr(blob,int4,int4)', '1.0','sql')
        then
            execute $q$
            CREATE OR REPLACE FUNCTION DBMS_LOB.substr (lob_loc IN BLOB,
                  amount  IN int4 := 32767,
                  p_offset  IN int4 := 1)
            returns RAW
            LANGUAGE sql
            IMMUTABLE NOT FENCED NOT SHIPPABLE
            AS $$
            SELECT rawout(pg_catalog.SUBSTR(rawsend(lob_loc),p_offset,amount))::text::raw;
            $$;
            $q$;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_lob.substr(blob,int4,int4): %', SQLERRM;
            rollback;
    end;
    -- =========================================================================

    -- =========================================================================
    -- dbms_lob.substr(clob,int4,int4)
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('FUNCTION', 'dbms_lob', 'substr(clob,int4,int4)', '1.0','sql')
        then
            execute $q$
            CREATE OR REPLACE FUNCTION DBMS_LOB.substr (lob_loc IN CLOB,
                  amount  IN int4 := 32767,
                  p_offset  IN int4 := 1)
            returns TEXT
            LANGUAGE sql
            IMMUTABLE NOT FENCED NOT SHIPPABLE
            AS $$
            SELECT pg_catalog.SUBSTR(lob_loc,p_offset,amount);
            $$;
            $q$;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_lob.substr(clob,int4,int4): %', SQLERRM;
            rollback;
    end;
    -- =========================================================================

    -- =========================================================================
    -- dbms_lob.instr(blob,raw,int4,int4)
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('FUNCTION', 'dbms_lob', 'instr(blob,raw,int4,int4)', '1.0')
        then
            execute $q$
            CREATE OR REPLACE FUNCTION DBMS_LOB.instr(lob_loc IN BLOB,
                                        pattern IN RAW,
                                        p_offset IN int4 := 1,
                                        nth IN int4 := 1)
            RETURNS integer
            LANGUAGE plpgsql STRICT IMMUTABLE
            AS $$
            DECLARE
                pos integer NOT NULL DEFAULT 0;
                occur_number integer NOT NULL DEFAULT 0;
                temp_str BYTEA;
                beg integer;
                i integer;
                length integer;
                ss_length integer;
                loc bytea;
            BEGIN
            loc:=rawsend(lob_loc);
                IF nth <= 0 THEN
                    RAISE 'argument ''%'' is out of range', occur_index
                    USING ERRCODE = '22003';
                END IF;

                IF p_offset > 0 THEN
                    beg := p_offset - 1;
                    FOR i IN 1..nth LOOP
                        temp_str := substring(loc FROM beg + 1);
                        pos := position(RAWSEND(pattern) IN temp_str);
                        IF pos = 0 THEN
                            RETURN 0;
                        END IF;
                        beg := beg + pos;
                    END LOOP;

                    RETURN beg;
                ELSIF p_offset < 0 THEN
                    ss_length := pg_catalog.length(RAWSEND(pattern));
                    length := pg_catalog.length(loc);
                    beg := length + 1 + p_offset;

                    WHILE beg > 0 LOOP
                        temp_str := substring(loc FROM beg FOR ss_length);
                        IF RAWSEND(pattern) = temp_str THEN
                            occur_number := occur_number + 1;
                            IF occur_number = nth THEN
                                RETURN beg;
                            END IF;
                        END IF;

                        beg := beg - 1;
                    END LOOP;

                    RETURN 0;
                ELSE
                    RETURN 0;
                END IF;
            END;
            $$ ;
            $q$;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_lob.instr(blob,raw,int4,int4): %', SQLERRM;
            rollback;
    end;
    -- =========================================================================

    -- =========================================================================
    -- dbms_lob.instr(clob,text,int4,int4)
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('FUNCTION', 'dbms_lob', 'instr(clob,text,int4,int4)', '1.0')
        then
            execute $q$
            CREATE OR REPLACE FUNCTION DBMS_LOB.instr(lob_loc CLOB, pattern TEXT,
                                p_offset integer, nth integer)
            RETURNS integer
            LANGUAGE plpgsql STRICT IMMUTABLE
            AS $$
            DECLARE
                pos integer NOT NULL DEFAULT 0;
                occur_number integer NOT NULL DEFAULT 0;
                temp_str TEXT;
                beg integer;
                i integer;
                length integer;
                ss_length integer;
                loc text;
            BEGIN
            loc:=lob_loc::TEXT;
                IF nth <= 0 THEN
                    RAISE 'argument ''%'' is out of range', occur_index
                    USING ERRCODE = '22003';
                END IF;

                IF p_offset > 0 THEN
                    beg := p_offset - 1;
                    FOR i IN 1..nth LOOP
                        temp_str := substring(loc FROM beg + 1);
                        pos := position(pattern IN temp_str);
                        IF pos = 0 THEN
                            RETURN 0;
                        END IF;
                        beg := beg + pos;
                    END LOOP;

                    RETURN beg;
                ELSIF p_offset < 0 THEN
                    ss_length := char_length(pattern);
                    length := char_length(loc);
                    beg := length + 1 + p_offset;

                    WHILE beg > 0 LOOP
                        temp_str := substring(loc FROM beg FOR ss_length);
                        IF pattern = temp_str THEN
                            occur_number := occur_number + 1;
                            IF occur_number = nth THEN
                                RETURN beg;
                            END IF;
                        END IF;

                        beg := beg - 1;
                    END LOOP;

                    RETURN 0;
                ELSE
                    RETURN 0;
                END IF;
            END;
            $$;
            $q$;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_lob.instr(clob,text,int4,int4): %', SQLERRM;
            rollback;
    end;
    -- =========================================================================

    -- =========================================================================
    -- dbms_lob.createtemporary(blob,bool,int4)
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('PROCEDURE', 'dbms_lob', 'createtemporary(blob,bool,int4)', '1.0')
        then
            execute $q$
            CREATE or replace  PROCEDURE DBMS_LOB.createtemporary(lob_loc IN OUT   BLOB,
                                        cache   IN            BOOLEAN,
                                        dur     IN            INT4 := 10) package
            AS
            BEGIN
            null;
            end;
            $q$;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_lob.createtemporary(blob,bool,int4): %', SQLERRM;
            rollback;
    end;
    -- =========================================================================

    -- =========================================================================
    -- dbms_lob.createtemporary(clob,bool,int4)
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('PROCEDURE', 'dbms_lob', 'createtemporary(clob,bool,int4)', '1.0')
        then
            execute $q$
            CREATE or replace  PROCEDURE DBMS_LOB.createtemporary(lob_loc IN OUT   CLOB,
                                        cache   IN            BOOLEAN,
                                        dur     IN            INT4 := 10) package
            AS
            BEGIN
            null;
            end;
            $q$;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_lob.createtemporary(clob,bool,int4): %', SQLERRM;
            rollback;
    end;
    -- =========================================================================

    -- =========================================================================
    -- dbms_lob.close(blob)
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('PROCEDURE', 'dbms_lob', 'close(blob)', '1.0')
        then
            execute $q$
            CREATE or replace  PROCEDURE DBMS_LOB.close(lob_loc IN OUT   BLOB)
            package
            AS
            BEGIN
            null;
            end;
            $q$;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_lob.close(blob): %', SQLERRM;
            rollback;
    end;
    -- =========================================================================

    -- =========================================================================
    -- dbms_lob.close(clob)
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('PROCEDURE', 'dbms_lob', 'close(clob)', '1.0')
        then
            execute $q$
            CREATE or replace  PROCEDURE DBMS_LOB.close(lob_loc IN OUT   CLOB)
            package
            AS
            BEGIN
            null;
            end;
            $q$;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_lob.close(clob): %', SQLERRM;
            rollback;
    end;
    -- =========================================================================

    -- =========================================================================
    -- dbms_lob.append(blob,blob)
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('PROCEDURE', 'dbms_lob', 'append(blob,blob)', '1.0')
        then
            execute $q$
            CREATE or replace  PROCEDURE DBMS_LOB.append(dest_lob IN OUT  BLOB,
                            src_lob  IN            BLOB) package
            AS
            BEGIN
            dest_lob:=rawout(rawsend(dest_lob)||rawsend(src_lob))::text::raw::blob;
            END;
            $q$;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_lob.append(blob,blob): %', SQLERRM;
            rollback;
    end;
    -- =========================================================================

    -- =========================================================================
    -- dbms_lob.append(clob,clob)
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('PROCEDURE', 'dbms_lob', 'append(clob,clob)', '1.0')
        then
            execute $q$
            CREATE or replace  PROCEDURE DBMS_LOB.append(dest_lob IN OUT  CLOB,
                            src_lob  IN            CLOB ) package
            AS
            BEGIN
            dest_lob:=(dest_lob::text||src_lob::text)::clob;
            END;
            $q$;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_lob.append(clob,clob): %', SQLERRM;
            rollback;
    end;
    -- =========================================================================

    -- =========================================================================
    -- dbms_lob.compare(blob,blob,int4,int4,int4)
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('FUNCTION', 'dbms_lob', 'compare(blob,blob,int4,int4,int4)', '1.0')
        then
            execute $q$
            CREATE OR REPLACE FUNCTION DBMS_LOB.compare(
                            lob_1    IN BLOB,
                            lob_2    IN BLOB,
                            amount   IN int4 := 2147483647,
                            offset_1 IN INTEGER := 1,
                            offset_2 IN INTEGER := 1)
            RETURNS int4
            LANGUAGE plpgsql
            IMMUTABLE NOT FENCED NOT SHIPPABLE
            AS $$
            DECLARE
            l_result int4 DEFAULT 0;
            l_r1 bytea DEFAULT ''::bytea;
            l_r2 bytea DEFAULT ''::bytea;
            len_1 int4;
            len_2 int4;
            lob1_bytea bytea;
            lob2_bytea bytea;
            begin
            lob1_bytea:=substr(rawsend(lob_1),offset_1,least(amount,pg_catalog.length(rawsend(lob_1))-offset_1+1));
            lob2_bytea:=substr(rawsend(lob_2),offset_2,least(amount,pg_catalog.length(rawsend(lob_2))-offset_2+1));

            len_1:=pg_catalog.length(lob1_bytea);
            len_2:=pg_catalog.length(lob2_bytea);

            if len_1<len_2 then
            return -1;
            elsif len_1>len_2 then
            return 1;
            end if;

            for i in 1..greatest(len_1,len_2)   LOOP
            l_r1:=pg_catalog.substr(lob1_bytea , i , 1);
            l_r2:=pg_catalog.substr(lob2_bytea , i , 1);
            if l_r1!=l_r2 THEN
            if l_r1>l_r2 then
            l_result:=1;
            else
            l_result:=-1;
            end if;
            EXIT;
            end if;
            end loop;
            return l_result;
            end;
            $$;
            $q$;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_lob.compare(blob,blob,int4,int4,int4): %', SQLERRM;
            rollback;
    end;
    -- =========================================================================

    -- =========================================================================
    -- dbms_lob.compare(clob,clob,int4,int4,int4)
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('FUNCTION', 'dbms_lob', 'compare(clob,clob,int4,int4,int4)', '1.0')
        then
            execute $q$
            CREATE OR REPLACE FUNCTION DBMS_LOB.compare(
                            lob_1    IN CLOB,
                            lob_2    IN CLOB,
                            amount   IN int4 := 2147483647,
                            offset_1 IN INTEGER := 1,
                            offset_2 IN INTEGER := 1)
            RETURNS int4
            LANGUAGE plpgsql
            IMMUTABLE NOT FENCED NOT SHIPPABLE
            AS $$
            DECLARE
            l_result int4 DEFAULT 0;
            l_r1 text DEFAULT ''::text;
            l_r2 text DEFAULT ''::text;
            len_1 int4;
            len_2 int4;
            lob1_text text;
            lob2_text text;
            begin
            lob1_text:=substr(lob_1::text,offset_1,least(amount,pg_catalog.length(lob_1::text)-offset_1+1));
            lob2_text:=substr(lob_2::text,offset_2,least(amount,pg_catalog.length(lob_2::text)-offset_2+1));

            len_1:=pg_catalog.length(lob1_text);
            len_2:=pg_catalog.length(lob2_text);

            if len_1<len_2 then
            return -1;
            elsif len_1>len_2 then
            return 1;
            end if;

            for i in 1..greatest(len_1,len_2)   LOOP
            l_r1:=pg_catalog.substr(lob1_text , i , 1);
            l_r2:=pg_catalog.substr(lob2_text , i , 1);
            if l_r1!=l_r2 THEN
            if l_r1::bytea>l_r2::bytea then
            l_result:=1;
            else
            l_result:=-1;
            end if;
            EXIT;
            end if;
            end loop;
            return l_result;
            end;
            $$;
            $q$;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_lob.compare(clob,clob,int4,int4,int4): %', SQLERRM;
            rollback;
    end;
    -- =========================================================================

    -- =========================================================================
    -- dbms_lob.copy(blob,blob,int4,int4,int4)
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('PROCEDURE', 'dbms_lob', 'copy(blob,blob,int4,int4,int4)', '1.0')
        then
            execute $q$
            CREATE or replace PROCEDURE DBMS_LOB.copy(dest_lob    IN OUT  BLOB,
                            src_lob     IN            BLOB,
                            amount      IN            INTEGER,
                            dest_offset IN            INTEGER := 1,
                            src_offset  IN            INTEGER := 1) package
            AS
            DECLARE
            dest_bytea bytea;
            end_bytea bytea DEFAULT ''::bytea;
            null_bytea bytea DEFAULT '\x00'::bytea;
            dest_bytea_length int4;
            pad_bytea bytea DEFAULT ''::bytea;
            BEGIN
            dest_bytea:=rawsend(dest_lob);
            dest_bytea_length:=pg_catalog.length(dest_bytea);
            if dest_bytea_length>dest_offset+amount-1 then
            end_bytea:=substr(dest_bytea,dest_offset+amount);
            end if;
            if dest_bytea_length<dest_offset THEN
            for i in 1..dest_offset-dest_bytea_length-1 LOOP
            pad_bytea:=pad_bytea||null_bytea;
            end loop;
            end if;

            dest_lob:=rawout(substr(dest_bytea,1,dest_offset-1)||pad_bytea||substr(rawsend(src_lob),src_offset,amount)||end_bytea)::text::raw::blob;
            end;
            $q$;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_lob.copy(blob,blob,int4,int4,int4): %', SQLERRM;
            rollback;
    end;
    -- =========================================================================

    -- =========================================================================
    -- dbms_lob.copy(clob,clob,int4,int4,int4)
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('PROCEDURE', 'dbms_lob', 'copy(clob,clob,int4,int4,int4)', '1.0')
        then
            execute $q$
            CREATE or replace PROCEDURE DBMS_LOB.copy(dest_lob    IN OUT  CLOB,
                            src_lob     IN            CLOB,
                            amount      IN            INTEGER,
                            dest_offset IN            INTEGER := 1,
                            src_offset  IN            INTEGER := 1) package
            AS
            DECLARE
            dest_text text;
            end_text text DEFAULT ''::text;
            null_text text DEFAULT ' '::text;
            dest_text_length int4;
            pad_text text DEFAULT ''::text;
            BEGIN
            dest_text:=dest_lob::text;
            dest_text_length:=pg_catalog.length(dest_text);
            if dest_text_length>dest_offset+amount-1 then
            end_text:=substr(dest_text,dest_offset+amount);
            end if;
            if dest_text_length<dest_offset THEN
            for i in 1..dest_offset-dest_text_length-1 LOOP
            pad_text:=pad_text||null_text;
            end loop;
            end if;

            dest_lob:=(substr(dest_text,1,dest_offset-1)||pad_text||substr(src_lob::text,src_offset,amount)||end_text)::clob;
            end;
            $q$;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_lob.copy(clob,clob,int4,int4,int4): %', SQLERRM;
            rollback;
    end;
    -- =========================================================================

    -- =========================================================================
    -- dbms_lob.erase(blob,int4,int4)
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('PROCEDURE', 'dbms_lob', 'erase(blob,int4,int4)', '1.0')
        then
            execute $q$
            CREATE or replace PROCEDURE DBMS_LOB.erase(lob_loc IN OUT   BLOB,
                            amount  IN OUT   INTEGER,
                            p_offset  IN      INTEGER := 1) package
            AS
            DECLARE
            lob_length int4;
            lob_bytea bytea;
            null_bytea bytea DEFAULT '\x00'::bytea;
            pad_bytea bytea DEFAULT ''::bytea;
            end_bytea bytea;
            begin
            lob_bytea:=rawsend(lob_loc);
            lob_length:=pg_catalog.length(lob_bytea);
            if amount<lob_length-p_offset+1 then
            end_bytea:=substr(lob_bytea,p_offset+amount+1);
            end if;
            amount:=least(amount,lob_length-p_offset+1);
            for i in 1..amount LOOP
            pad_bytea:=pad_bytea||null_bytea;
            end loop;
            lob_loc:=rawout(pg_catalog.substr(lob_bytea,0,p_offset-1)||pad_bytea||end_bytea)::text::raw;
            end;
            $q$;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_lob.erase(blob,int4,int4): %', SQLERRM;
            rollback;
    end;
    -- =========================================================================

    -- =========================================================================
    -- dbms_lob.erase(clob,int4,int4)
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('PROCEDURE', 'dbms_lob', 'erase(clob,int4,int4)', '1.0')
        then
            execute $q$
            CREATE or replace PROCEDURE DBMS_LOB.erase(lob_loc IN OUT   CLOB,
                            amount  IN OUT   INTEGER,
                            p_offset  IN      INTEGER := 1) package
            AS
            DECLARE
            lob_length int4;
            lob_text text;
            null_text text DEFAULT ' '::text;
            pad_text text DEFAULT ''::text;
            end_text text;
            begin
            lob_text:=lob_loc::text;
            lob_length:=pg_catalog.length(lob_text);
            if amount<lob_length-p_offset+1 then
            end_text:=substr(lob_text,p_offset+amount+1);
            end if;
            amount:=least(amount,lob_length-p_offset+1);
            for i in 1..amount LOOP
            pad_text:=pad_text||null_text;
            end loop;
            lob_loc:=(pg_catalog.substr(lob_text,0,p_offset-1)||pad_text||end_text)::clob;
            end;
            $q$;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_lob.erase(clob,int4,int4): %', SQLERRM;
            rollback;
    end;
    -- =========================================================================

    -- =========================================================================
    -- dbms_lob.read(blob,int4,int4,raw)
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('PROCEDURE', 'dbms_lob', 'read(blob,int4,int4,raw)', '1.0')
        then
            execute $q$
            CREATE or replace PROCEDURE DBMS_LOB.read(lob_loc IN            BLOB,
                            amount  IN OUT  INTEGER,
                            p_offset  IN            INTEGER,
                            buffer  OUT           RAW) package
            as
            DECLARE
            tmp_bytea bytea;
            BEGIN
            tmp_bytea:=pg_catalog.substr(rawsend(lob_loc),p_offset,amount);
            if pg_catalog.length(tmp_bytea)=0 or tmp_bytea is null
            then raise NO_DATA_FOUND;
            end if;
            buffer:=rawout(tmp_bytea)::text::raw;
            amount:=pg_catalog.length(tmp_bytea);
            end;
            $q$;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_lob.read(blob,int4,int4,raw): %', SQLERRM;
            rollback;
    end;
    -- =========================================================================

    -- =========================================================================
    -- dbms_lob.read(clob,int4,int4,text)
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('PROCEDURE', 'dbms_lob', 'read(clob,int4,int4,text)', '1.0')
        then
            execute $q$
            CREATE or replace PROCEDURE DBMS_LOB.read(lob_loc IN            CLOB,
                            amount  IN OUT  INTEGER,
                            p_offset  IN            INTEGER,
                            buffer  OUT           TEXT) package
            as
            BEGIN
            buffer:=pg_catalog.substr(lob_loc::text,p_offset,amount);
            if pg_catalog.length(buffer)=0 or buffer is null
            then raise NO_DATA_FOUND;
            end if;
            amount:=pg_catalog.length(buffer);
            end;
            $q$;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_lob.read(clob,int4,int4,text): %', SQLERRM;
            rollback;
    end;
    -- =========================================================================

    -- =========================================================================
    -- dbms_lob.trim(blob,int4)
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('PROCEDURE', 'dbms_lob', 'trim(blob,int4)', '1.0')
        then
            execute $q$
            CREATE or replace PROCEDURE DBMS_LOB.trim(lob_loc IN OUT   BLOB,
                            newlen  IN            INTEGER) package
            as
            BEGIN
            lob_loc:=rawout(pg_catalog.substr(rawsend(lob_loc),1,newlen))::text::raw::blob;
            end;
            $q$;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_lob.trim(blob,int4): %', SQLERRM;
            rollback;
    end;
    -- =========================================================================

    -- =========================================================================
    -- dbms_lob.trim(clob,int4)
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('PROCEDURE', 'dbms_lob', 'trim(clob,int4)', '1.0')
        then
            execute $q$
            CREATE or replace PROCEDURE DBMS_LOB.trim(lob_loc IN OUT   CLOB,
                            newlen  IN            INTEGER) package
            as
            BEGIN
            lob_loc:=pg_catalog.substr(lob_loc::text,1,newlen)::clob;
            end;
            $q$;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_lob.trim(clob,int4): %', SQLERRM;
            rollback;
    end;
    -- =========================================================================

    -- =========================================================================
    -- dbms_lob.write(blob,int4,int4,raw)
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('PROCEDURE', 'dbms_lob', 'write(blob,int4,int4,raw)', '1.0')
        then
            execute $q$
            CREATE or replace PROCEDURE DBMS_LOB.write(lob_loc IN OUT   BLOB,
                            amount  IN            INTEGER,
                            p_offset  IN            INTEGER,
                            buffer  IN            RAW) package
            AS
            BEGIN
            DBMS_LOB.copy(lob_loc,buffer::blob,amount,p_offset,1);
            end;
            $q$;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_lob.write(blob,int4,int4,raw): %', SQLERRM;
            rollback;
    end;
    -- =========================================================================

    -- =========================================================================
    -- dbms_lob.write(clob,int4,int4,text)
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('PROCEDURE', 'dbms_lob', 'write(clob,int4,int4,text)', '1.0')
        then
            execute $q$
            CREATE or replace PROCEDURE DBMS_LOB.write(lob_loc IN OUT   cLOB,
                            amount  IN            INTEGER,
                            p_offset  IN            INTEGER,
                            buffer  IN            text) package
            AS
            BEGIN
            DBMS_LOB.copy(lob_loc,buffer::text,amount,p_offset,1);
            end;
            $q$;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_lob.write(clob,int4,int4,text): %', SQLERRM;
            rollback;
    end;
    -- =========================================================================

    -- =========================================================================
    -- dbms_lob.writeappend(blob,int4,raw)
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('PROCEDURE', 'dbms_lob', 'writeappend(blob,int4,raw)', '1.0')
        then
            execute $q$
            CREATE or replace PROCEDURE DBMS_LOB.writeappend(lob_loc IN OUT  BLOB,
                                    amount  IN     INTEGER,
                                    buffer  IN     RAW) package
            AS
            /*未做amount校验 by DarkAthena 2022-05-03*/
            BEGIN
            lob_loc:=RAWOUT(RAWSEND(lob_loc)||RAWSEND(buffer))::TEXT::RAW::BLOB;
            end;
            $q$;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_lob.writeappend(blob,int4,raw): %', SQLERRM;
            rollback;
    end;
    -- =========================================================================

    -- =========================================================================
    -- dbms_lob.writeappend(clob,int4,text)
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('PROCEDURE', 'dbms_lob', 'writeappend(clob,int4,text)', '1.0')
        then
            execute $q$
            CREATE or replace PROCEDURE DBMS_LOB.writeappend(lob_loc IN OUT  CLOB,
                                    amount  IN     INTEGER,
                                    buffer  IN     TEXT) package
            AS
            /*未做amount校验 by DarkAthena 2022-05-03*/
            BEGIN
            lob_loc:=(lob_loc::TEXT||buffer)::CLOB;
            end;
            $q$;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_lob.writeappend(clob,int4,text): %', SQLERRM;
            rollback;
    end;
    -- =========================================================================

    -- =========================================================================
    -- dbms_snapshot.refresh(text,text)
    -- =========================================================================
    begin
        if compat_tools.drop_compat_package('PROCEDURE', 'dbms_snapshot', 'refresh(text,text)', '1.0')
        then
            execute $q$
            create or replace PROCEDURE dbms_snapshot.refresh(list   IN text,
                                                method IN text := 'F') package
            is
            l_exists int;
            l_search_path text;
            begin
            l_search_path:=current_setting('search_path');
            FOR rec IN
            ( SELECT regexp_substr(view_name, '(.+)(?=\.)') schema_name,
                    CASE WHEN regexp_substr(view_name, '(.+)(?=\.)') IS NULL
                            THEN view_name
                            ELSE regexp_replace(view_name, '(.+)(\.)(.+)', '\3')
                            END view_name
                FROM (SELECT unnest(string_to_array(list, ',')) view_name) )
            loop

                if rec.schema_name is null then
                for rec2 in (select trim(unnest(string_to_array(l_search_path,','))) schema_name) loop
                    select count(1)  into  l_exists from pg_catalog.gs_matviews
                    where schemaname= case when rec2.schema_name ='"$user"' then user else rec2.schema_name end
                    and  matviewname=rec.view_name;
                    if l_exists>0 then
                    rec.schema_name:=case when rec2.schema_name ='"$user"' then user else rec2.schema_name end;
                    exit;
                    end if;
                end loop;
                end if;

                select count(1)  into  l_exists from pg_catalog.gs_matviews
                    where schemaname=rec.schema_name
                    and  matviewname=rec.view_name;

                if l_exists=0 then
                raise 'MATERIALIZED VIEW %.% does not exist',rec.schema_name,rec.view_name;
                end if;

                IF UPPER(method) = 'F' THEN
                EXECUTE immediate 'REFRESH INCREMENTAL MATERIALIZED VIEW '|| rec.schema_name||'.'||rec.view_name;
                ELSIF UPPER(method) = 'C' THEN
                EXECUTE immediate 'REFRESH MATERIALIZED VIEW '||rec.schema_name||'.'|| rec.view_name;
                ELSE
                raise 'illegal argument for function';
                END IF;

            END LOOP;
            END;
            $q$;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_snapshot.refresh(text,text): %', SQLERRM;
            rollback;
    end;
    -- =========================================================================


end;
$PKG_CREATION$ language plpgsql;


-- Show result & Exit
do $RESULT_SUMMARY$
declare
    l_app_name  text  := current_setting('application_name');
begin
    set client_min_messages='notice';
    if l_app_name not in ('runMe', 'checkMe')
    then
        raise notice '';
        raise notice '-- =====================================================================';
        raise notice '-- Compat Object List: ';
        raise notice '-- =====================================================================';
        for l_app_name in select '   |' || pad_char
                              || rpad(coalesce(object_type, ' '), max_object_type, pad_char) || pad_char || '|' || pad_char
                              || rpad(coalesce(object_name, ' '), max_object_name, pad_char) || pad_char || '|' || pad_char
                              || rpad(coalesce(object_version, ' '), max_object_version, pad_char) || pad_char || '|' || pad_char
                              || rpad(coalesce(object_language, ' '), max_object_language, pad_char) || pad_char || '|' || pad_char
                              || rpad(coalesce(operation, ' '), max_operation, pad_char) || pad_char || '|' as result_data
                            from (select greatest(max(length(object_type)), 5) max_object_type
                                       , greatest(max(length(object_name)), 6) max_object_name
                                       , greatest(max(length(case when local_version = script_version then local_version else local_version || ' => ' || script_version end)), 7) max_object_version
                                       , greatest(max(length(case when local_language = script_language then local_language else local_language || ' => ' || script_language end)), 8) max_object_language
                                       , greatest(max(length(operation)), 9) max_operation
                                    from temp_result) l
                            join (select 'type' as object_type
                                       , 'name' as object_name
                                       , 'version' as object_version
                                       , 'language' as object_language
                                       , 'operation' as operation
                                       , ' ' as pad_char
                                   union all
                                  select '-' as object_type
                                       , '-' as object_name
                                       , '-' as object_version
                                       , '-' as object_language
                                       , '-' as operation
                                       , '-' as pad_char
                                   union all
                                  select object_type, object_name
                                       , case when local_version = script_version then local_version else local_version || ' => ' || script_version end as object_version
                                       , case when local_language = script_language then local_language else local_language || ' => ' || script_language end as object_language
                                       , operation, ' ' from temp_result) r on 1 = 1
        loop
            raise notice '%', l_app_name;
        end loop;
    end if;
end;
$RESULT_SUMMARY$ language plpgsql;

\q
